# Excel’s WEEKDAY Function In depth

Today we shall have an in-depth look at another underutilized yet powerful function, especially when analyzing dates–**WEEKDAY**.

I hope Part 1 of this series made you get acquainted with **MOD** function and it has become a favorite for you.

Below are some of the uses of WEEKDAY function:

- Summarize by weekday or weekend
- Count-if by Weekday or Weekend
- Highlight Weekends or Weekdays
- Count the number of specific weekdays/weekends between two dates with formula
- Find the Nth Day of the Week of Any Month

Basically, WEEKDAY returns the day of the week as a Number between 0 and 7.

`Syntax = WEEKDAY(serial_number, [return_type])`

- Serial_Number refers to the date to fetch the day of the week
- Return type as shown below determines the day to use as the first day of the week in assigning the numbers.

NB: the Same date returns different day of the week numbers based on the return type.** 1 is the default return type.**

Download the Worksheet to follow Examples

**Create Summaries by Weekday or Weekend**

When used together with SUMPRODUCT, WEEKDAY can be used to summarize data by days of the week.

For example, how do you know which is your best sales day?

From the above, we can tell Saturday is our Best sales day by using below formula

=SUMPRODUCT(--(WEEKDAY(salesdate,2)=dayNumber)*Values)

How it Works:

- (WEEKDAY(salesdate,2)=dayNumber)
**→→**generates an array of TRUE and FALSE e.g. based on date number

` `**{FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE....}**

- This Boolean array is converted to Numbers equivalent by use of double negatives (double unary Method).

{0;1;0;1;0;1;0.....}

- SUMPRODUCT gets the sum of the product of this array of numbers and Qty Sold.

=SUMPRODUCT({0;1;0;1;0;1;0....},{49,49,27,49,49,49,12....})

**Count If by Day of the Week**

Still based on above example, what if you wanted to know the number of transactions per day of the week.

You can use SUMPRODUCT and WEEKDAY again to count the number of day of week there are in all these sales transactions

=SUMPRODUCT(- -(WEEKDAY(salesdate,2)=dayNumber))

**How it Works:**

As outlined above but now SUMPRODUCT just sums the array of 1 and 0 **{0;1;0;1;0;1;0;1;1;0….}**

**Create a Custom Day of the Week Name**

With the help of another Overlooked function–CHOOSE, WEEKDAY can be used to create custom names.

For example return day of the week in Swahili

=CHOOSE(WEEKDAY(A3,2),"Jumatatu", "Jumanne", "Jumatano", "Alhamisi", "Ijumaa", "Jumamosi", "Jumapili")

**Highlight Weekends or Weekdays**

Go to Conditional formatting→→New Rule→→Use Formula to Determine which Cell to Format

Apply this format **=WEEKDAY(firstCell with Date,2)>5**→→This will format weekends

For formatting, Weekdays use this **=WEEKDAY(firstCell with Date,2)<=5**

Finally, Select the area to apply the format

**Count the number of specific weekdays/weekends between two dates with formula**

There are two ways of counting these days:

- using a combination of INT and WEEKDAY→→

=INT((WEEKDAY(StartDate-DayofWeek)-StartDate + EndDate))/7)

- Or combine,SUMPRODUCT,WEEKDAY,ROW & INDIRECT→→

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(StartDate &":"& EndDate)))=DayofWeek))

**Find the Nth Day of the Week of Any Month of Any Year**

How can you Schedule meetings on every **4th** **Wednesday of Every month** for the **year 2017**?

=DATE(year,month,1+7*N^{th}Day)-WEEKDAY(DATE(year,month,8-DayofWeek),2)

For example to calculate 4th Wednesday for May ** =Date(2017,5,1+7*4)-WEEKDAY(DATE(2017,5,8-3),2)**

### How it Works:

See this link for full explanation

Download the Worksheet to see above Examples

## RECOMMENDATION

7 OVERLOOKED USES OF EXCEL MOD FUNCTION

7 WAYS TO USE EXCEL REPT FUNCTION