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.
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.
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.