Calculating Time By 15 minute periods

[Matt G]

Hi

I have a excel worksheet and I am looking to calulate time by a 15 minute period. To give a better idea, take a look at the way my spreadsheet is set up below.

Idle Start Idle End Duration 08:00:00 08:15:00 08:30:00 08:45:00 09:00:00
08:12:00 09:00:48 00:48:48
09:22:23 09:24:56 00:02:33
10:16:45 10:32:32 00:15:47


What I need is a formula to calculate how long during each 15 minute period was spent as idle (needs to calculate and show as the example below)

Idle Start Idle End Duration 08:00:00 08:15:00 08:30:00 08:45:00 09:00:00
08:12:00 09:00:48 00:48:48 00:03:00 00:15:00 00:15:00 00:15:00 00:00:48

The idle start column is where the idle time begins.
The idle end column is where the idle time finishes.
The duration column is the total time spent idle.

If you need any further information let me know. Thanks.

Matt

 

[Ron Rosenfeld]

Hi

I have a excel worksheet and I am looking to calulate time by a 15 minute period. To give a better idea, take a look at the way my spreadsheet is set up below.

Idle Start Idle End Duration 08:00:00 08:15:00 08:30:00 08:45:00 09:00:00
08:12:00 09:00:48 00:48:48
09:22:23 09:24:56 00:02:33
10:16:45 10:32:32 00:15:47


What I need is a formula to calculate how long during each 15 minute period was spent as idle (needs to calculate and show as the example below)

Idle Start Idle End Duration 08:00:00 08:15:00 08:30:00 08:45:00 09:00:00
08:12:00 09:00:48 00:48:48 00:03:00 00:15:00 00:15:00 00:15:00 00:00:48

The idle start column is where the idle time begins.
The idle end column is where the idle time finishes.
The duration column is the total time spent idle.

If you need any further information let me know. Thanks.

Matt

I think this will work:

Assumptions:

1. Idle Start is in Column A
2. Idle End is in Column B
3. Duration is in Column C
4. Times start in Column D and continue over to one entry past the last time.
In other words, if the latest time is 10:57, the times continue over until
11:00.


In D2 (under the 8:00:00) put the formula:

=MAX(0,E$1-MAX($A2,D$1))-MAX(0,E$1-MAX($B2,D$1))

Make sure the relative/absolute references are as they are in the formula.

Drag/copy it across and down as needed.


--ron

 

[J.E. McGimpsey]

One way:

Assuming that your table starts in A1:

=MAX(0,MIN(D$1+TIME(0,15,0),$B2)-MAX($A2, D$1))

Note1: this will work up until midnight.

Note2: I used D$1+TIME(0,15,0) rather than E$1 so that you didn't
need an extra time column after the last period. You could
substitute E$1 to make it a bit more efficient.