How to use the WEEKDAY function

The WEEKDAY function allows you to convert all dates into a number from 1 to 7 representing their weekday, from Monday to Sunday.

The standard use of the WEEKDAY function is


This has the week starting on Sunday so Sunday dates return 1, Mondays return 2 up to Saturdays, which return 7.

In terms of handling weekdays (Monday to Friday), it would be easier to have Monday = 1 and Sunday = 7. This means the weekends are the two highest values in the week and if they need to be avoided we can simply use a greater than comparison to ignore weekends.

There is an optional argument in the WEEKDAY function that uses this numbering convention.


As an example, if cell A1 contains a date, then the following formula will identify that date as a weekday or a weekend date.


Another Example


In the example above cell A1 is the input cell, cell A5 contains a reasonably simple formula that identifies the person rostered for the date in A1, by using the WEEKDAY function with the INDEX function.


The INDEX function allows you specify a range, a single column range in this case D2:D8, and extract a row number from it. Since the list starts on a Monday the WEEKDAY function (using the 2 on the end) provides the correct number to extract the person’s name.

If you are wondering cell A2 is linked to cell A1, but has the Custom Format dddd – see below.

I have written two new posts for INTHEBLACK website that demonstrate more uses of the WEEKDAY function. Both of these include videos. See below for links.

Find the first weekday in a month

Find the last weekday in the month


Please note: I reserve the right to delete comments that are offensive or off-topic.