WEEKDAY Function in Excel – what is this function and what can you use it for in your spreadsheets
This week’s hint and tip is on the WEEKDAY function in Excel. This function can be used to return the day of the week as a number relating to a date in your spreadsheets. This isn’t covered in our courses so we decided to do a hint and tip on it. We are going to go through it now below.
WEEKDAY Function
This function returns the day of the week from a date in your spreadsheet. The day is returned as a number and by default ranges from 1 (Sunday) to 7 (Saturday). We are going to go through it’s syntax below:
Function layout: =WEEKDAY(serial_number,[return_type])
- serial_number: this is required. This is either the cell reference where the date is located or you can enter the date be using the DATE function (e.g. for 17th June 2025 you would use DATE(2025,6,17)
- [return_type]: this is optional. This is where you can type in a number from a table to determine the type of return for the formula
Worked example
We are going to go through an example of this function now below.
The formula we are going to use is: =WEEKDAY(B1,2). This formula has the cell reference filled in for the first part and then the number 2 selected from the table of return type options. The return type options are listed below and come up when you are typing it in.
Return type options:
- 1 – Numbers 1 (Sunday) to 7 (Saturday)
- 2 – Numbers 1 (Monday) to 7 (Sunday)
- 3 – Numbers 0 (Monday) through 6 (Sunday)
- 11 – Numbers 1 (Monday) to 7 (Sunday)
- 12 – Numbers 1 (Tuesday) through 7 (Monday)
- 13 – Numbers 1 (Wednesday) through 7 (Tuesday)
- 14 – Numbers 1 (Thursday) through 7 (Wednesday)
- 15 – Numbers 1 (Friday) to (Thursday)
- 16 – Numbers 1 (Saturday) through 7 (Sunday)
- 17 – Numbers 1 (Sunday) to 7 (Saturday)
Once the function is filled in, it returns the number for the day shown in column B. You then copy it down to fill in the rest of the cells.
Conditional Formatting
Now that we have created the formula, we are going to apply conditional formatting to the cells to fill grey the weekend days. Firstly select column C. Then go to conditional formatting, highlight cell rules and choose ‘New Rule’ option. From here set up the following rule, ‘cell value greater than 5’ and the formatting being a grey full colour. The rule can be seen below when you select the manage rules option.
Once applied, the conditional formatting can be seen on the cells as shown in the screenshot below.
The video below shows you what the WEEKDAY function in Excel is and an example of how it can be used. There are lots of functions in Excel that can be used in many ways in your spreadsheets.
Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on the WEEKDAY Function in Excel. Why not take a look at our previous video hint and tip on creating a Table of Contents in Word?