# 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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Replies
4
Views
355
Replies
1
Views
114
Replies
5
Views
210
Replies
1
Views
126
Replies
2
Views
191

1,127,516
Messages
5,625,256
Members
416,085
Latest member
Jlex

### 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