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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,627
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCFG
1Date1Date2Datex
227-Nov-201-Mar-2531-Dec-205Dec-31
331-Dec-2031-Dec-2511Dec 31 and annual birthday
1ee
Cell Formulas
RangeFormula
F2F2=(B2-A2+1)-NETWORKDAYS.INTL(A2,B2,"0000000",Hol_Dec31)
F3F3=(B3-A3+1)-NETWORKDAYS.INTL(A3,B3,"0000000",Hol)
Named Ranges
NameRefers ToCells
Hol='1ee'!$L$2:$L$24F2:F3
Hol_Dec31='1ee'!$L$2:$L$13F2:F3
 

luke_101

New Member
Joined
Nov 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Thanks Peter for your in depth analysis. I'll study the different approaches to le
 

luke_101

New Member
Joined
Nov 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,088
Members
416,010
Latest member
NJT

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
Top