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

=WEEKDAY(A1)

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.

=WEEKDAY(A1,2)

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

=IF(WEEKDAY(A1,2)>5,”weekend”,”weekday”)

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.

=INDEX(D2:D8,WEEKDAY(A1,2))

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