In this post we will see how to check if a date is falling on weekend.
We have see n that excel has some built in function to check if date is weekday or not but here we will try to figure out if a date is Saturday or Sunday i.e. weekend.
We will take use of weekday function in excel to determine if a date is falling on Saturday or Sunday.
Below is the data table in excel that shows day’s name in column A and the actual dates in column B and we would like to create a function in column C that will take the dates in column B as input and will return the result as TRUE if the date is on weekends i.e. Saturday or Sunday or FALSE if the date is not falling on weekends.
Step 1
Insert the formula below to determine if a date belongs to weekend in excel.
=MOD(WEEKDAY(B2), 7) < 2
This formula has two inbuilt functions, MOD which gives the remainder left after we divide a number by divisor and WEEKDAY function that returns the number of the day in a week starting from 0 for Saturday, 1 for Sunday and so on.
Let’s understand what is happening in the formula to check if date belongs to weekend or not.
For the date value 23-12-2021 which is Thursday, WEEKDAY function will return value of 5 and if we divide 5 by 7 the remainder would be 5 which is not less than 2 and hence the result would be FALSE but it will calculate to TRUE for date value 25-12-2021 as WEEKDAY function will return 0 and the final result would be less than 2.
Hope this helped.