Formula for finding Date difference between 2 dates

[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...

 

[Stefi]

=A2-A1-SUMPRODUCT(--(E1:E5>A1),--(E1:E5<A2))
Regards,
Stefi

„Saji†ezt írta:

 

[Bob Phillips]

=N(A2-A1-(COUNTIF(E1:E5,">="&A1)-COUNTIF(E1:E5,">="&A2)))

 

[Dave Curtis]

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

 

[Saji]

Hi Stefi,

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