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]

Can anyone help on ths please

 

[mikerickson]

You could use = B1-A1 and format the cell with the custom format [h]

If you need the number of hours returned =ROUND(24*(B1-A1),0) should do it.

 

[kizzie37]

thanks mikerickson, but how does this work with the difference in dates and the in and out of hours requirement

 

[mikerickson]

I missed that you posted the cell locations in the OP,
Try = A3-A2

about in/out hours, will there ever be a case where both ends of the interval are IN, but an OUT period exists in side.

eg Monday 3PM to Tuesday 2PM (with overnight OUT inside the inverval)

 

[Jon von der Heyden]

Try;

=(INT(B1)-INT(A1))*(17-9)/24+MEDIAN(0,(17-9)/24,MOD(B1,1)-9/24)-MEDIAN(0,MOD(A1,1)-9/24,(17-9)/24)

for the 'in' hours...

Edit:

'Out' hours = B1-A1-C1
where C1 is the result of the first formula above...

 

[kizzie37]

mikerickson

yes this could potentially happen

 

[Jon von der Heyden]

In my example change B1 to A3...

A1 start date & time
A3 end date & time

 

[kizzie37]

many thanks, seems to work well

 

[barry houdini]

Can the start time be on a Friday and the end time on a Monday or similar? In that case you'd have to include NETWORKDAYS function.

Will start time/date and end time/date always be within working hours?