time interval between dates incl weekends excl holidays

D

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

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
 
M

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
 
T

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
 
T

Tim

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

Best wishes,

Tim
 
D

douwe

Hi Tim,

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

D.
 
M

Mike H

formula was missing a set of brackets

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

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