Method to count a specific day of the week between two dates

Today we are going to discuss about how to count a specific day of the week between two dates. To be specific, Let's take the count of Fridays between two dates.

Here, requirement is pretty simple and straight forward. From date and to date is provided, we have to find the number of Fridays available in between the given days. The solution is bit wild but effective.

Lot to learn, let's dive in.

Formula in C5 cell is,

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C3&":"&C4)),2)=MATCH($B$5,$G$3:$G$9,0))*1)

To add more dynamics to our requirement, we will use data validation in B5 cell, to select any day from Monday to Sunday. List range for data validation lies at G3:G9.

Let me deconstruct this process into steps,

1. Get the sequence of days between given dates.

2. Get the weekday (1,2,3...) for those sequence of days.

3. Match with weekday of given criteria (here it is Friday).

4. Count the matching occurrences of weekday.

Step-1:

To get the sequence of days between the given dates, we are going to use the construction of Row and Indirect function.

INDIRECT function expects text reference and converts that into range reference. Here, we concatenate C3 cell value, colon (":") and C4 cell value. Formulas don't see formatting and so the date (01/01/2024) in C3 cell will be looked as 45292 and the date (09/04/2024) in C4 cell will be looked as 45391. When we concatenate these two numbers with Colon (":"), it becomes text and output of this portion is "45292:45391". If we feed this into INDIRECT function, text part will get transformed into range reference. Now INDIRECT function will hold the range from row 45292 to 45391.

ROW function helps us to get the row numbers from 45292 to 45391, Output of Row and Indirect construction will look like below,

Now, you can see that first step in our requirement is accomplished,

Step-2:

WEEKDAY function has 2 arguments,

=WEEKDAY(serial_number,[return_type])

We are going to wrap this sequence of days into serial_number argument,

return_type argument allows us to select the start day of the week, in our case we fed 2, that means our week starts from Monday.

This function will pick the number from serial_number argument, then finds the date behind the number, after that gets the day of the date (Monday, Tuesday....). If it is Monday, this function will return 1 and so on. See below picture.

Step-3:

MATCH function has 3 arguments,

=MATCH(lookup_value,lookup_array,[match_type])

MATCH($B$5,$G$3:$G$9,0)

Here, our goal is to find the weekday of given criteria which is Friday (B5).

MATCH function picks B5 cell for lookup_value argument,

List range at G3:G9 acts as lookup_array argument,

We fed zero in match type argument, so we force MATCH function to do exact match.

MATCH function will deliver the relative position of Friday in the list range G3:G9, which is 5.

Step-4:

We are going to compare the weekday of sequence of days with weekday of our criteria day (Friday). This logical operation converts our weekday of sequence of days into Boolean values. If weekday is 5 then TRUE else FALSE, Output of this logical operation shown below.

IF we do some mathematical operation, these Boolean values get converted into numbers. TRUE into 1 and FALSE into 0.

SUMPRODUCT function will sum these values, since we have one's and zero's, this summing operation will act as counting operation and will throw below output.