Caluculate difference between two times

[BRO]

I am looking for a formula to calculate the difference between two times in
hours. I also want to exclude the time between 11 PM on Friday night and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

 

[daddylonglegs]

assuming that neither of your start or end date/times will fall between 11 PM
Friday and 11 PM Sunday you can use this formula

=(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-MOD(A1+"1:00",1))*24

note: NETWORKDAYS is part of Analysis ToolPak. If you get #NAME! error with
the above then you need to install. Tools > addins > tick "Analysis ToolPak"

 

[Roger Govier]

Hi

Assuming you have the Analysis Toolpak loaded, Tools>Addins>Analysis
Toolpak, then

=NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
If you want to exclude Holidays as well as weekends, then add the 3rd
parameter to Networkdays which can either be cell references holding a
list of holiday dates or a named range holding holiday dates

=NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
where X1:X9 hold a list of holiday dates

 

[BRO]

The formula work great for most of my data, however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop
date however will never fall in this time period.

Any ideas for these cases?

 

[BRO]

The formula worked great. Any suggestions for when the start date would fall
between 11 PM Friday night and 11 PM Sunday? On ocassion the start date
would fall in this time period but the stop date would not. On these
ocassion the total time would start from 11 PM Sunday night.

Thanks,

 

[Roger Govier]

Hi

I think the following will work, making use of a "helper cell". I used
A2.
In A2
=IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24>=23),
WEEKDAY(A1,2)>5),DATE(YEAR(A1),
MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1)

Then in cell to have the result
=IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24)

 

[daddylonglegs]

Hello BRO

"however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop
date however will never fall in this time period"

just amend my suggested formula to:

=NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1)

 

[daddylonglegs]

Sorry, should be multiplied by 24 as per original...

=(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1))*24