Formula for finding Date difference between 2 dates
 

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

Formula for finding Date difference between 2 dates
 

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

€žSaji€� ezt Ã*rta:

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

Formula for finding Date difference between 2 dates
 

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

--
__________________________________
HTH

Bob

"Saji" wrote in message
...
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...

Formula for finding Date difference between 2 dates
 

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" wrote:

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

Formula for finding Date difference between 2 dates
 

Hi Stefi,

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

"Stefi" wrote:

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

€žSaji€� ezt Ã*rta:

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