You may need to count the occurrences of a specific weekday between two dates in Excel. For example, how many Fridays between two dates.
There is no real standalone function in Excel to do this, but it can be done. This could be a useful formula to find how many payments, or how many meetings until an end date.
How Many Fridays Until a Specific Date – Excel Formula
The formula below compares the dates in cells A2 and B2 and returns the the number of Fridays between them. The formula is explained below.
=B2-A2-NETWORKDAYS.INTL(A2,B2,16)+1
In this formula, the start date is subtracted from the end date. This leaves us with how many days in total between the two dates.
In the second half of the formula, the NETWORKDAYS.INTL function is used to calculate the difference between the two dates excluding Fridays. This is then subtracted from the current total to leave us with how many Fridays there are.
The 16 in the function specifies to exclude the Fridays. When typing the function a list appears asking which days to exclude in the weekend argument.
The +1 is added to the end because the NETWORKDAYS.INTL function calculates whole workdays. For example, you may consider the difference in days between today and tomorrow to be 1 day. NETWORKDAYS.INTL would return that answer as 2 as it uses each day as a whole day. So the +1 is added to counteract that.
Wonderful Quick Tip for Achieving This
I wanted to explain another technique to count the number of times a specific day of week occurs between two dates.
The NETWORKDAYS.INTL function is used, and in the third argument a string of 1’s and 0’s are used to specify which day of week to count. The example below counts the number of Fridays again.
The string starts from a Monday. So this can easily be customised to your own requirements.
Entering a 1 specifies a non working day, and 0 specifies a working day. The 0 in the fifth position identifies Friday only as the working day.
=NETWORKDAYS.INTL(A2,B2,"1111011")
More Excel Formula Tutorials
- Five Awesome Date Functions in Excel
- Timesheet with Different Rates for Shift Work
- How Many Months Between Two Dates
The Quick Tip works in most cases but if the 1st of the month is the day after the day you want to count (i.e. Saturday in your example) the formula returns a count that is one too high. For example May 2021 has four Fridays but 01 May 2021 is a Saturday, the formula returns 5 for May.
The following addition to the formula appears to fix the problem
=NETWORKDAYS.INTL(A2,B2,”1111011″)-IF(WEEKDAY(EOMONTH(B2,-1))=6,1,0)
Thank you for the tip, Chris.
I just checked the tip on the blog post with the 01/05/2021 and the 30/05/2021 and it successfully returned 4, so I don’t know why it didn’t work for you.