CODEWITHSUNDEEP
excelexcel-formula
Muhammad Shehata
Muhammad Shehata

Reputation: 25

Excel formula to count days intersecting between different ranges of date

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

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.

Upvotes: 1

Views: 108

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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():

enter image description here

• 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))

enter image description here

Upvotes: 1

Related Questions