Excel Tip: Using the weekday of a date in a function

In an earlier post, I showed a method for pulling the text of the weekday out of a date. This is all very well if you are just wanting to pull the name of the weekday out of the date, but if you want to do any calculations using this information, then working with the day name is a bit "clunky".

As usual, Excel offers a different approach that is more appropriate.

The WEEKDAY function pulls the day out of the date as a number from 1 to 7 or 0 to 6, with options of which weekday to start on.

The syntax of the WEEKDAY function is as follows:

=WEEKDAY(Date,[Option])

Without the optional second argument, this will return a number 1 to 7 for the weekday of the date in the first argument, with Sunday being 1. You can, however, change how it works by using any of the following as the second argument:

1 or omitted - Numbers 1 (Sunday) to 7 (Saturday).

2 - Numbers 1 (Monday) to 7 (Sunday).

3 - Numbers 0 (Monday) to 6 (Sunday).

11 - Numbers 1 (Monday) to 7 (Sunday).

12 - Numbers 1 (Tuesday) to 7 (Monday).

13 - Numbers 1 (Wednesday) to 7 (Tuesday).

14 - Numbers 1 (Thursday) to 7 (Wednesday).

15 - Numbers 1 (Friday) to 7 (Thursday).

16 - Numbers 1 (Saturday) to 7 (Friday).

17 - Numbers 1 (Sunday) to 7 (Saturday).

So, for example, we can determine whether the date in cell A1 is a weekend with a formula such as:

=IF(WEEKDAY(A1,2)>5,"Weekend","Work")

If you need a refresher on the IF function, take a look at this earlier post.

By entering 2 as the second argument, Saturday and Sunday will be 6 and 7 respectively, so we can apply the criteria >5 to identify a weekend.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".