Does a date fall on a weekend?
In this article, I'll explain how you can find out if a date falls on a weekend or not. For this we use a simple formula.
For convenience, you can use the sample file. This can be downloaded at the bottom of this page.
The formula
To find out whether a date falls on a weekend, we use the formula/function below.
WEEKDAY(serial_number;[type_number])The function is structured as follows with the following arguments:
- serial number. This argument is required and must be a serial number. A date in a cell that is formatted as text therefore does not work. You will first have to convert this to a date format. A date that is formatted as a date is seen in Excel as a serial number and therefore works.
- type_number. This argument is optional and determines the result. With this argument you can indicate what the first day of the week is.
Results WEEKDAY()
| wdt_ID | type_result | Result |
|---|---|---|
| 1 | 1 | A number from 1 (Sunday) to 7 (Saturday). This corresponds to previous versions of Microsoft Excel. |
| 2 | 2 | A number from 1 (Monday) to 7 (Sunday). |
| 3 | 3 | A number from 0 (Monday) to 6 (Sunday). |
| 4 | 11 | A number from 1 (Monday) to 7 (Sunday). |
| 5 | 12 | A number from 1 (Tuesday) to 7 (Monday). |
| 6 | 13 | A number from 1 (Wednesday) to 7 (Tuesday). |
| 7 | 14 | A number from 1 (Thurs) to 7 (Wednesday). |
| 8 | 15 | A number from 1 (Friday) to 7 (Thursday). |
| 9 | 16 | A number from 1 (Saturday) to 7 (Friday). |
| 10 | 17 | A number from 1 (Sunday) to 7 (Saturday). |
How does the formula work
At the top formula =WEEKDAY(B1) Sunday is the first day of the week with the value 1. October 28, 2022 falls on a Friday. If one starts counting at 1 with Sunday, Friday is the sixth day.
In the middle formula =WEEKDAY(B1;2) Monday is the first day of the week with the value 1. In this case, Friday is the fifth day.
In the bottom formula =WEEKDAY(B1;3) Monday is the first day of the month, but has 0 as its initial value. If you start counting on Monday with 0, Friday is the fourth day.
Determine the weekend
To determine whether a date falls on a weekend, we can use the function =WEEKDAY() combine with the OR() function.
The formula then becomes:
=OR(WEEKDAY(DATE)={1;7})If the date falls on a Sunday (1), the first condition is met and is then true. If the date falls on a Saturday (7), then the second condition is met and is therefore true. If one of the two conditions WHERE is, the result is also WHERE. If both conditions FALSE will be the outcome FALSE to be.
Downloads
If you can't figure it out with the above guide alone, download the sample file or ask a question by leaving a message.