WEEKDAY Function in Excel – Two Examples

The WEEKDAY function in Excel is one of its many wonderful date and time functions. These functions are some of the most popular in Excel.

The Excel WEEKDAY function returns a number that identifies the day of the week from a given date. It has several options to specify which day is the first day of the week.

This Excel function is very useful. This tutorial will show two examples of the Excel WEEKDAY function helping us to achieve typical Excel tasks.

WEEKDAY Function in Excel Anatomy

The syntax of the WEEKDAY function in Excel is as follows. It contains two arguments.

=WEEKDAY(serial number, [return type])

The serial number is simply the date that you want to return the weekday for.

The return type is a number from 1 to 7 that identifies the day of the week of the given date. It provides a list so that you can choose how you would like the number returned e.g. Sunday = 1 to Saturday = 7, or Monday = 1 to Sunday = 7.

Using the WEEKDAY Function to Calculate Weekend Rates

One situation where you may want to use the WEEKDAY function is to calculate a different rate of pay for weekend work.

In the list below, the WEEKDAY function has been used with the IF function in column C to calculate a different rate of pay when working on a Saturday (these dates are in my local DMY format).

This was achieved my using the formula below in cell C2. This formula checks if the day of the week is equal to 7 (which indicates a Saturday). If this is true then the hours worked is multiplied by the Saturday rate, and if not then the hours worked is multiplied by the standard workday hourly rate.

=IF(WEEKDAY(A2,1)=7,B2*$F$3,B2*$F$2)

In this list, the 04/02/2017 and the 11/02/2017 were a Saturday. You can see that Excel applied the increased rate of pay.

This example can be seen in action on my Excel timesheet for different rates for shift work post.

Highlight the Saturday and Sundays in a List

In this second example, we will highlight the dates in a list that fall on a Saturday or a Sunday.

For this, we will need to use an Excel formula that includes WEEKDAY and the OR function within a Conditional Formatting rule.

1. Select the range of dates you want to format.
2. Click the Home tab, Conditional Formatting button and then New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the following formula in the box provided. Adjust the cell in the formula to the first cell of the selected range of cells.

=OR(WEEKDAY(A2,1)=1,WEEKDAY(A2,1)=7)

The following image shows the formatted weekend dates.

In this formula, the OR function is used to test more than one condition. Although a range of dates was highlighted, we only reference cell A2 in the formula because it is the first cell of that range.

The return type of 1 was used in the Excel WEEKDAY function. This means that the first day of the week is a Sunday.

If we had used return type 2, we could have written the following simpler formula. However, it is fun to explore other scenarios and to learn the OR function too.

=WEEKDAY(A2,2)>5

I used this technique of formatting weekend dates in my Excel Gantt Chart template.

More Excel Function Tutorials

• Calculate how many Fridays between two dates
• Excel formula for elapsed time in days, hours and minutes
• 5 awesome date functions
• Separate text into different cells
• The TEXT function in Excel