# Yuck. Time in increments problem

#### SandsB

##### Well-known Member
I have thousands or rows of data that include a start time and a duration. Something like this:
A2 = Bob
B2 = 8/15/2019 6:25:19 AM
C2 = 12:38:13 AM (This appears on the screen as 0:38:13, the actual duration, and has a format of h:mm:ss)

To that raw data, a formula in Column D takes the time in column B and gives me the 30 minute time interval when the event started by using this formula:=IF(A2="","",FLOOR(MOD(D2,1),"0:30"))
So in this example, it comes back with 6:00 AM.

Every day I get a few thousand more rows of data, append it to the bottom of my file and update a pivot table to show how much time particular tasks took during 30 minute intervals by person, day of the week, etc.

My problem is that when my pivot table looks at the duration of the event in the example above, it tells me that 00:38:13 of the 30 minutes between 06:00 and 06:30 were spent on that task. More than 30 minutes of a 30 minute increment because the duration carried over to a different interval. And some events could last hours so a 4 hour event is shown as happening all in one 30 minute increment and the other 7 increments (at least) would show no time. I don't see a way to resolve this without adding new rows of data under every row that has a duration that carries over into the next 30 minute increment. Then dividing the duration up so each increment is correct. So, using this example, the current row and the new rows would look like this because the duration spans 3 increments:

A2 = Bob
B2 = 8/15/2019 6:25:19 AM
C2 = 00:04:41 AM
D2 = 06:00 AM

A3 = Bob
B3 = 8/15/2019 6:30:00 AM
C3 = 00:30:00 AM
D3 = 06:30 AM

A4 = Bob
B4 = 8/15/2019 7:00:00 AM
C4 = 00:03:32 AM
D4 = 07:00 AM

Is there a way to automate this or is there something that would work better?

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Replies
4
Views
392
Replies
1
Views
135
Replies
14
Views
197
Replies
8
Views
96
Replies
5
Views
265

1,129,388
Messages
5,636,032
Members
416,892
Latest member
Bensch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back