How to calculate number of occurrence of a specific month between 2 dates?

luke_101

New Member
Joined
Nov 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm new in this forum and wanted to share a solution to a problem that took me some brain twisting to solve: how to calculate the number of occurrences of say your wife's birthday or Christmas eve or year end bonus etc... between 2 dates. Apparently an easy one but it's not. At least it has not been an easy nut to crack for me.
I'll share my solution here and would like to see if anyone else has a similar or different and more efficient solution to this.

I decided after somme wrong routes to simplify the problem by reducing it to a whole month. This was OK for me because it had to do with yearly payments occurring at a specific month between 2 dates. So basically, the proposed algorithm will count number of say month of december between 2 dates that are also reduced to their respective month.

Here is the solution with entire thinking process:

1606487617647.png

My apologies for the entire solution as image but did not see another option.
I'd be glad to share above in excel format with all formulas so you can play with it, but again as new user of this forum, I did not find a way to attach a file.

So here you go. Comments welcome.

Luke_101
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to Mr Excel, Luke :)

You can't attach a workbook to the forum, but you can post them in a way that you can copy and paste directly to excel (with formulas) by using the XL2BB add in (see link in my signature block below).

This is what it looks like when you post it, I've used my own take on your idea for the example. Quite often formulas like this can produce discrepancies when the start / end dates are the same as the date of interest, rows 3 to 5 below are there to check for these discrepancies.

Note that if you have office 365 dynamic arrays then it might be necessary to confirm the formula as legacy array by use of Ctrl Shift Enter on a single cell before filling down.
Book1
ABCD
1Date1Date2DatexDays
227/11/202001/03/202531-Dec5
331/12/202031/12/202531-Dec6
401/01/202131/12/202531-Dec5
531/12/202030/12/202531-Dec5
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),MONTH(C2),DAY(C2)))+1
Press CTRL+SHIFT+ENTER to enter array formulas.


A little addition to show the flexibility of the different approach, which I couldn't see a way to do with your method, lets say that you get your bonus twice a year as in the example below, the array formula allows for multiple dates by using the holidays parameter of the networkdays.intl function. I believe that this is working correctly but have only done a very quick test in order to post it before the 10 minute window to edit my reply expires.
Book1
ABCDE
1Date1Date2DatexDateyDays
227/11/202001/03/202531-Dec30-Jun9
331/12/202031/12/202531-Dec30-Jun11
401/01/202131/12/202531-Dec30-Jun10
531/12/202030/12/202531-Dec30-Jun10
Sheet1
Cell Formulas
RangeFormula
E2:E5E2=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),MONTH(C2:D2),DAY(C2:D2)))+1
Press CTRL+SHIFT+ENTER to enter array formulas.

I've only expanded to 2 dates, but this should work with any number of fixed date occasions, it would not be good for anything where the date moves from year to year.
 
Last edited:
Upvote 0
Another approach (non-volatile, normally entered):
Excel Formula:
=SUMPRODUCT(--(TEXT(ROW(INDEX(A:A,A2):INDEX(B:B,B2)),"dd-mmm")=TEXT(C2,"dd-mmm")))
 
Upvote 0
Welcome to the MrExcel board!

Another option, if you have the LET() function in your Excel 365
Excel Formula:
=LET(seq,SEQUENCE(B2-A2+1,,A2),f,"d-m",COUNT(FILTER(seq,TEXT(seq,f)=TEXT(C2,f))))

If you didn't have the LET function the full version would be
Excel Formula:
=COUNT(FILTER(SEQUENCE(B2-A2+1,,A2),TEXT(SEQUENCE(B2-A2+1,,A2),"d-m")=TEXT(C2,"d-m")))

.. or since you have a formal table structure, the conversions would be ..

20 11 28.xlsm
ABCDE
1Date1Date2DatexTotal1Total2
227/11/20201/03/202531-Dec55
Count between Dates
Cell Formulas
RangeFormula
D2D2=LET(d_1,[@Date1],d_2,[@Date2],seq,SEQUENCE(d_2-d_1+1,,d_1),f,"d-m",COUNT(FILTER(seq,TEXT(seq,f)=TEXT([@Datex],f))))
E2E2=COUNT(FILTER(SEQUENCE([@Date2]-[@Date1]+1,,[@Date1]),TEXT(SEQUENCE([@Date2]-[@Date1]+1,,[@Date1]),"d-m")=TEXT([@Datex],"d-m")))
 
Last edited:
Upvote 0
Thanks so much everyone! I'll have to dig into your answers to try understand them and learn from the exercice. I'm not as much of a power user as you guys...
Let me look at Jasonb75 solution first: {=(B5-A5)-NETWORKDAYS.INTL(A5,B5,"0000000",DATE(ROW(INDIRECT(YEAR(A5)&":"&YEAR(B5))),MONTH(C5),DAY(C5)))+1}
I already struggle here. Can you explain the logic?
In what is supposed to contain holidays exclusion list I see : DATE(ROW(INDIRECT(YEAR(A5)&":"&YEAR(B5))),MONTH(C5),DAY(C5)). You lost me.

Txs!

Luc.
 
Upvote 0
I've kind of reversed the logic of the normal process, the holiday exclusion list actually contains the dates to be counted rather than excluded.

ROW(INDIRECT(YEAR(A5)&":"&YEAR(B5))) is generating an array of years, with a 2020 date in A5 and a 2025 date in B5, the array would be {2020,2021,2022,2023,2024,2025}. The DATE function then adds the month and day from C5 to each year in the array to create an array of the dates that should be counted as the holiday exclusion list.
"0000000" in Networkdays.intl sets a 7 day working week (no weekends).

The formula as a whole works on the basis that subtracting the days that you don't want counted from the total number of days, leaves you with the number of days that you do want counted.

For example, a period of 5 years is ~1826 days. Subtracting the start date from the end date will give a result of 1825 (which is the reason for adding 1 at the end). If there are 5 days that should be counted then the networkdays part of the formula will count 1821. 1826 - 1821 gives you the 5 days required.

Hopefully that makes a little bit of sense, I was getting myself confused typing it :eek:
 
Upvote 0
I've kind of reversed the logic of the normal process, the holiday exclusion list actually contains the dates to be counted rather than excluded.

ROW(INDIRECT(YEAR(A5)&":"&YEAR(B5))) is generating an array of years, with a 2020 date in A5 and a 2025 date in B5, the array would be {2020,2021,2022,2023,2024,2025}. The DATE function then adds the month and day from C5 to each year in the array to create an array of the dates that should be counted as the holiday exclusion list.
"0000000" in Networkdays.intl sets a 7 day working week (no weekends).

The formula as a whole works on the basis that subtracting the days that you don't want counted from the total number of days, leaves you with the number of days that you do want counted.

For example, a period of 5 years is ~1826 days. Subtracting the start date from the end date will give a result of 1825 (which is the reason for adding 1 at the end). If there are 5 days that should be counted then the networkdays part of the formula will count 1821. 1826 - 1821 gives you the 5 days required.

Hopefully that makes a little bit of sense, I was getting myself confused typing it :eek:
I understand it now. Very clever! I was not familiar with creating an array like you did with ROW(INDIRECT()). Is there a good documentation that illustrate this somewhere? I suspect this technique could help me with other problems.
 
Upvote 0
I was not familiar with creating an array like you did with ROW(INDIRECT()). ... I suspect this technique could help me with other problems.
You have Excel 365. It has the SEQUENCE function that does the same thing and without the volatile function INDIRECT.
If you did need such a sequence in an earlier version, Tetra201 has shown how to create one in those versions without the volatility also.

In fact, due to the volatile function in jasonb75's suggestion, I had drafted a considerably long reply here suggesting to avoid that volatile function and wondering if you had tried any of the other non-volatile suggestions made by Tetra201 and myself thinking that they would definitely be preferable. Luckily I did a bit of last-minute testing and found that the speed of the NETWORKDAYS suggestion was immensely faster that all the other suggestions so scrapped my initial draft.

With only 50 rows of data the time-lag to calculate the SUMPRODUCT version and the COUNT/FILTER versions was noticeable whereas the NETWORK days results appeared instantly. On measuring, on my machine, Tetra201's formula and all mine were of the order of 120 - 150 times slower than jasonb75's!! ?

So pursuing the ingenious NETWORKDAYS idea but with the tools available in your Excel 365, here is a non-volatile alternative using the SEQUENCE function. This also seems to be marginally faster again, but not by a long way with my 50 rows. This example did throw up one apparent anomaly as highlighted below. The example may not be possible with your data but I would have thought both highlighted rows should return 1, which does happen with the SEQ version. I'm not actually sure why the ROW(INDIRECT version returns 0 for row 6 and 1 for row 7 as stepping through the formula with the Evaluate Formula tool seems to calculate all the intermediate steps within the NETWORKDAYS function correctly for both rows but not the final result of it for row 6. :confused:

luke_101.xlsm
ABCDE
1Date1Date2Datexpost 2SEQ Alternative
227/11/20201/03/202531-Dec55
331/12/202031/12/202531-Dec66
41/01/202131/12/202531-Dec55
531/12/202030/12/202531-Dec55
614/08/202115/08/202115-Aug01
716/08/202117/08/202117-Aug11
815/08/202117/07/202215-Aug11
Count between Dates (3)
Cell Formulas
RangeFormula
D2:D8D2=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),MONTH(C2),DAY(C2)))+1
E2:E8E2=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(SEQUENCE(YEAR(B2)-YEAR(A2)+1,,YEAR(A2)),MONTH(C2),DAY(C2)))+1
 
Upvote 0
I'm not actually sure why the ROW(INDIRECT version returns 0 for row 6 and 1 for row 7
Peter, I had expected something like this which was the reason for my comment in post 2
Note that if you have office 365 dynamic arrays then it might be necessary to confirm the formula as legacy array by use of Ctrl Shift Enter on a single cell before filling down.
As you will see below the formula returns 1 on that row when CSE confirmed.

Also, I've added the same formula using INDEX instead of INDIRECT in order to remove the much disliked volatility. Not sure why I used INDIRECT to begin with, it is a function that I prefer to avoid using.
Book1 (version 1).xlsb
ABCDE
1Date1Date2Datexpost 2Non-Volatile
227/11/202001/03/202531/12/202055
331/12/202031/12/202531/12/202066
401/01/202131/12/202531/12/202055
531/12/202030/12/202531/12/202055
614/08/202115/08/202115/08/202011
716/08/202117/08/202117/08/202011
815/08/202117/07/202215/08/202011
Sheet5
Cell Formulas
RangeFormula
D2:D8D2=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),MONTH(C2),DAY(C2)))+1
E2:E8E2=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(ROW(INDEX(E:E,YEAR(A2)):INDEX(E:E,YEAR(B2))),MONTH(C2),DAY(C2)))+1
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top