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

[luke_101]

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:

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

 

[Peter_SSs]

As you will see below the formula returns 1 on that row when CSE confirmed.

That is interesting as Excel 365 is supposed to (I thought) evaluate all formulas as array formulas, and in my limited experience that has been the case - until this one. I can confirm that cell D6 does return 1 for me too in Excel 365 if I confirm with CSE. But still no idea why one row worked without it and one didn't.

 

[jasonb75]

Excel 365 is supposed to (I thought) evaluate all formulas as array formulas

I don't have 365, but my understanding is that the dynamic array works the same as committing an array to a multiple cell range rather than a single cell, then filling down.

But still no idea why one row worked without it and one didn't.

That bit has me confused as well, I was wondering if it might be one of those fluke results when an unconfirmed array happens to be in the perfect relative position to give the correct result, although I'm not seeing how it would be possible in this instance.

 

[Dave Patton]

I had a post yesterday but it did not go through the internet.
The question was "how to calculate the number of occurrences of say your wife's birthday or Christmas eve or year end bonus" I extended the challenge from or to and. I read the question as how many particular dates such as Dec 31 and/or person's annual birthday etc
are in a range. I put the target dates in a range named Hol. For just Dec 31, I used a different named range.
The formula is not array entered.

T202012a.xlsm
	A	B	C			F	G
1	Date1	Date2	Datex
2	27-Nov-20	1-Mar-25	31-Dec-20			5	Dec-31
3	31-Dec-20	31-Dec-25				11	Dec 31 and annual birthday
1ee

Cell Formulas
Range	Formula
F2	F2	=(B2-A2+1)-NETWORKDAYS.INTL(A2,B2,"0000000",Hol_Dec31)
F3	F3	=(B3-A3+1)-NETWORKDAYS.INTL(A3,B3,"0000000",Hol)

Named Ranges
Name	Refers To	Cells
Hol	='1ee'!$L$2:$L$24	F2:F3
Hol_Dec31	='1ee'!$L$2:$L$13	F2:F3

 

[luke_101]

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.

luke_101.xlsm
	A	B	C	D	E
1	Date1	Date2	Datex	post 2	SEQ Alternative
2	27/11/2020	1/03/2025	31-Dec	5	5
3	31/12/2020	31/12/2025	31-Dec	6	6
4	1/01/2021	31/12/2025	31-Dec	5	5
5	31/12/2020	30/12/2025	31-Dec	5	5
6	14/08/2021	15/08/2021	15-Aug	0	1
7	16/08/2021	17/08/2021	17-Aug	1	1
8	15/08/2021	17/07/2022	15-Aug	1	1
Count between Dates (3)

Cell Formulas
Range	Formula
D2:D8	D2	=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),MONTH(C2),DAY(C2)))+1
E2:E8	E2	=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(SEQUENCE(YEAR(B2)-YEAR(A2)+1,,YEAR(A2)),MONTH(C2),DAY(C2)))+1

Thanks Peter for your in depth analysis. I'll study the different approaches to le

 

[luke_101]

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.

luke_101.xlsm
	A	B	C	D	E
1	Date1	Date2	Datex	post 2	SEQ Alternative
2	27/11/2020	1/03/2025	31-Dec	5	5
3	31/12/2020	31/12/2025	31-Dec	6	6
4	1/01/2021	31/12/2025	31-Dec	5	5
5	31/12/2020	30/12/2025	31-Dec	5	5
6	14/08/2021	15/08/2021	15-Aug	0	1
7	16/08/2021	17/08/2021	17-Aug	1	1
8	15/08/2021	17/07/2022	15-Aug	1	1
Count between Dates (3)

Cell Formulas
Range	Formula
D2:D8	D2	=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),MONTH(C2),DAY(C2)))+1
E2:E8	E2	=(B2-A2)-NETWORKDAYS.INTL(A2,B2,"0000000",DATE(SEQUENCE(YEAR(B2)-YEAR(A2)+1,,YEAR(A2)),MONTH(C2),DAY(C2)))+1

Hi Peter. Previous answer got posted too fast before finishing my sentence... I'll try all proposed solutions to learn from them.
Is there a way to edit a post after posting?

 

[Peter_SSs]

Is there a way to edit a post after posting?

For members who have very few posts: No
Once you have a certain minimum number of posts: You have 10 minutes from when you first make a post. In that time an 'Edit' link should appear near the bottom left of your post.