time interval between dates incl weekends excl holidays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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
 
Now I’ll go to watch the movie on the TV. Tomorrow will check if my answer
was helpful.

Best wishes,

Tim
 
formula was missing a set of brackets

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