calculating peak time

[kalanr]

I've got a data series with timestamps. e.g.

change date time elapsed
24/07/2011 16:35:20
26/07/2011 11:09:19 42:33:59
26/07/2011 11:09:27 0:00:08
28/07/2011 19:31:12 56:21:45
28/07/2011 19:31:16 0:00:04
29/07/2011 07:25:03 11:53:47

to calculate the elapsed time between two timestamps is easy enough, but now I'd have to split the elapsed time to two componenets - time elapsed in peak time (6:00 - 22:00), and off-peak time (22:00-6:00). I've been looking around, but found only solutions events happening on the same day.

Any ideas? I'm open to VBA solution as well

 

[barry houdini]

Hello kalanr, welcome to MrExcel

I assume that peak time is 06:00 to 22:00 on all seven days of the week?

If your dates are in A2 down, times in B2 down and the total elapsed time in C3 down then try this formula in D3 for 06:00 to 22:00 (peak) hours.

=(A3-A2)*("22:00"-"6:00")+MEDIAN(B3,"6:00","22:00")-MEDIAN(B2,"6:00","22:00")

and clearly the 22:00 to 06:00 hours are all the rest so E3 can be just

=C3-D3

format both of those cells as [h]:mm:ss and copy down the column

 

[kalanr]

Works like a charm. And it's much simpler, than the solution I started to work on. I really need to get some rest. Luckily it's friday.

thanks