Formula for finding Date difference between 2 dates

  • Thread starter Thread starter Saji
  • Start date Start date
S

Saji

Hi,

Please help me to find a formula for calculating the number of days between
2 dates excluding the dates given in a range. Networkdays function doesnt
work as it excludes Saturday & sundays.

Example:

Cell A1 = 3-Mar-2009
Cell A2 = 15 March 2009

My Holiday list contains in Column Range E1:E5
E1 = 6-Mar-2009
E2 = 7-Mar-2009
E3 = 13-Mar-2009
E4 = 20-Mar-2009
E5 = 27-Mar-2009

Answer should be 15 - 3 - (3 Holidaysfalls between the Dates )) = 9 days.

Can Anuone help me please . I am desperately looking for an answer...
 
Hi, Here are two more ways you coud use.

=DATEDIF(A1,A2,"d")-COUNTIF(E1:E5,"<"&A2)-COUNTIF(E6:E10,"<="&A1)
or

=DATEDIF(A1,A2,"d")-SUM(($E$1:$E$5>=A1)*($E$1:$E$5<=A2)*1)

The latter on is an array formula, entered with Control-Shift-Enter.

Dave
 
Hi Stefi,

Thanks a lot.. This worked fine. I was blowing my mind with netwrokdays and
I didnt reach anywhere. Thank you very much..
 
Back
Top