Yuck. Time in increments problem

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
616
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?
 

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top