How many Sundays are there between 2/19 and 5/22/2022? Have you ever been faced with such a question during one of your data analysis tasks?
Unfortunately, there’s no native function in Excel that gives count of a specific weekday between two dates. But now, with the Advanced Formula Environment (AFE) you can build your own custom function that can behave like a native one across your workbook, or even can be shared with others, too.
In the video, I show the steps to define CountWeekday function using the AFE. You can also copy and paste the code below to replicate the same in your environment.
If you’re curious to know the anatomy of the function, here is a description of how it works:
First, the function receives StartDate, EndDate, and WeekdayNumber as three input parameters. The first two define the period in which the count should take place. The third parameter is for the number of the day in question (1: Sun, 2: Mon, 3: Tue…,7: Sat). It then creates a range of dates (as rows) between the Start and End Dates. After that, it runs a logical formula (True/False) on the weekday of each date in the range-it converts logicals into 1’s and 0’s for use in the SUM function. At the end, it sums up the 1’s to give the count of weekdays that match the WeekdayNumber parameter that you specified.
Looks confusing? Not after you watch the video and try it yourself.
Copy and paste the below code in your Advanced Formula Environment to replicate the function.
CountWeekday = LAMBDA(StartDate, EndDate, WeekdayNumber,
LET(
DateRange, ROW(INDIRECT(StartDate & ":" & EndDate)), WeekdayRange, WEEKDAY(DateRange), WeekdayFound, N(WeekdayRange=WeekdayNumber), CountWeekday, SUM(WeekdayFound), CountWeekday
)
);
