time interval between dates incl weekends excl holidays

[douwe]

Hi,

Can anyone help me on the calculation of the time elipsed between start
date/time and end date/time, inculding weekend days and excluding holidays. I
have tried using NETWORKDAYS; but that can't handle the weekend days. Tried
using WORKDAY; but there I cannot subtract the two dates.

 

[Tim]

Hi,

Go to Tool>Add-Ins and check>Analysis ToolPak and then use NETWORKDAYS .
Assuming your start date is in A1 end date is in B1 the formula is
=NETWORKDAYS(A1,B1)

Tim

 

[Mike H]

I'm sure someones going to come up with something much more elegant but try:-

=((A2-A1)*24)-(COUNTIF(C1:C8,"<"&A2)*24)-(COUNTIF(C1:C8,"<"&A1)*24)

Start date and time in A1
End date and time in A2
Holiday list in C1 to C8 (allows 24 hrs for each holiday)
Format formula as general

Mike

 

[Tim]

More detailed example. Assuming in cell A3 is 8-Aug-07 and in cell A2 is
9-Aug-07 this formula =NETWORKDAYS(A3,A2) will give you the total of 2 days.
Format the cell with the formula as a General Number.

Regards,

Tim

 

[Tim]

Now I’ll go to watch the movie on the TV. Tomorrow will check if my answer
was helpful.

Best wishes,

Tim

 

[douwe]

Hi Tim,

Thanks for your reply; however Networkdays wil not work with weekend days.

D.

 

[Mike H]

formula was missing a set of brackets

=((A2-A1)*24)-((COUNTIF(C1:C8,"<"&A2)*24)-(COUNTIF(C1:C8,"<"&A1)*24))

 

[douwe]

Hi Mike,

This wil work just fine; pure and simple. Thanks!
D.