Excel formula to count days intersecting between different ranges of date

Question

I’m looking for an Excel formula to count days that intersect between a given range (Date1 & Date2) and any of Ramadan’s date ranges (given in a table). Meanwhile, exclude the weekends (Sat & Sun).

See photo below:

Days Intersecting

I tried using a formula: =NETWORKDAYS.INTL(F3,F4,1)

But this gives only delta days between the two given dates = 10 days. I need the intersection days with any of Ramadan's Date ranges excluding the weekends (Sat & Sun). The expected answer should be 6 days.

This kind of stuff is probably best done by a macro.. You create a series of 0's and 1's per year.. 366 of them.. The 0's would represent Sat/Sun.. If it's not a leap year the last char can be ignored. Then you count the number of 1's between 2 dates .. and convert the daynumber in the year back to a date. — JGFMK
– JGFMK, Commented May 5, 2024 at 17:26

Mayukh Bhattacharya · Accepted Answer · 2024-05-05 18:16:08Z

1

If I have understood correctly, you are looking for a formula like this which should accomplish the desired output, using NETWORKDAYS.INTL() with a combination of MAX() + MIN():

• Formula used in cell E6

=LET(
      _Date1, F3,
      _Date2, F4,
      _RamadanStarts, B3:B13,
      _RamadanEnds, C3:C13,
      _Ramadan, XLOOKUP(YEAR(MAX(_Date1,_Date2)),YEAR(_RamadanStarts),_RamadanStarts:_RamadanEnds),
      NETWORKDAYS.INTL(MAX(_Date1,TAKE(_Ramadan,,1)),MIN(TAKE(_Ramadan,,-1),_Date2),1))

edited May 5, 2024 at 18:16

answered May 5, 2024 at 15:40

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel formula to count days intersecting between different ranges of date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related