Check Weekend Dates In Excel

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.

Excel showing dates to be checked for 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.

Excel formula to check if a date belongs to weekend

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.




Result showing weekend dates are highlighted

Hope this helped.




Share The Knowledge

Random Posts