Formula for finding Date difference between 2 dates

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

Stefi

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

„Saji†ezt írta:
 
D

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
 
S

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top