Calculating Time By 15 minute periods

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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
 
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.
 
Back
Top