Duration Formula

[kizzie37]

I have the below Date & time in cell a1 and another date and time in cell a3

8/2/2010 08:45:00
8/2/2010 10:15:00


I need to be able to show duration (in hours) between the 2 times, in this instance its the same date, but this wont always be the case. To add a level of complexity in addition to the gross duration in hours, I will also need to know how long (in hours or mins) of the duration fits into "in hours" ( 9 -5 mon- fri) then the duration thats fits "out of hours" (after 5pm weekdays or all day Sat & Sun)

Appreciate this wont be all in one formula but can anyone suggest how to work through this Please

 

[kizzie37]

yes, that could also be a possibility. How/where would I add this in?

 

[barry houdini]

You can use this formula to get the business hours, assuming A2 contains start time/date and B2 end time/date, where both will be in business hours

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"9:00")+MOD(B2,1)-MOD(A2,1)

 

[kizzie37]

Thanks to all for this, I have another query though on variance of duration.


Cell b1 = Target Time ( this will be a number of hours, in this case 3)
Cell C1 = Duration (uses one of the formulas above, again a number of hours)


Problem is if the duration is over the target time, the value is displayed as ########

How can I display the minus variance, i need to report how much time duration over target.

Any ideas