This blog post is about an Excel formula which will return the Count of a particular Weekday or Weekend between two dates.
The following formula will give us the Count of ‘Sundays’ between ’01/01/2022′ and ’31/12/2022′.
=SUMPRODUCT(--(WEEKDAY(SEQUENCE("31-12-2022"-"01-01-2022"+1,,"01-01-2022"))=1))
Let’s see how this formula works.
Table of Contents
Explanation of the Formula
The SEQUENCE function will generate a list of dates from 01/01/2022 and 31/12/2022.
=SEQUENCE("31-12-2022"-"01-01-2022"+1,,"01-01-2022",1)
The WEEKDAY function in Excel will return a number from 1 to 7 corresponding the Date supplied to it.
For example, the following formula will return 7, which is equivalent to Saturday.
=WEEKDAY("31-12-2022")
When the array of dates created using the SEQUENCE function is supplied into the WEEKDAY function, the result is an array of values from 1 to 7.
=WEEKDAY(SEQUENCE("31-12-2022"-"01-01-2022"+1,,"01-01-2022",1))
We need the count of Sundays and 1 is the code corresponding to Sunday.
When this array of values is equated with 1, the result is another array of TRUE and FALSE values.
=WEEKDAY(SEQUENCE("31-12-2022"-"01-01-2022"+1,,"01-01-2022"))=1
The formula returns TRUE against 1 and FALSE for all other values.
The count of TRUE values in this array is equivalent to the number of Sundays. To get that count, we will use the SUMPRODUCT function in Excel.
=SUMPRODUCT(--(WEEKDAY(SEQUENCE("31-12-2022"-"01-01-2022"+1,,"01-01-2022"))=1))
Count of Wednesdays between two dates
We have these dates ’11-04-2022′ and ’15-09-2022′ in the cells C2 and C3. Following is the formula to find the count of Wednesdays between these two dates.
Note that 4 is the code for Wednesday.
Count of Weekends between two dates
If you want the Count of multiple days, specify the corresponding day codes in a array.
Suppose, I want to know the count of Sundays and Saturdays between ’11-04-2022′ and ’15-09-2022′. Following is the formula for the same.
=SUMPRODUCT(--(WEEKDAY(SEQUENCE("15-09-2022"-"11-04-2022"+1,,"11-04-2022"))={1,7}))
Note that 1 is the code for Sunday and 7 for Saturday.