Breaking down rows based on hours and days

**Serdica** · 03-15-2013, 12:27 AM

Don't know how to display data it in pivot table that doesn't exist in the org source but

I have data that looks like this:
#,Day, Date and time, minutes
1,Tue, 1/1/2012 1:00 am, 60
2,Wed, 1/2/2012 3:00 am, 120
3,Wed, 1/2/2012 2:00 pm, 190
4,Wed, 1/2/2012 5:00 pm, 30

What I would like to do is take the data and break anything over 60 in multiple rows. So where it says 120, it will create a copy of the row and new row below the original with 60 min and 3:00 am (instead of 2:00 am). The field that has 190 min. It will split the row in to three additional rows below, then at 2:00 pm will have 60 then at 3:00 it will be another 60, then at 4:00 will have 60 and at 5:00 it will have 40.

So the end result should look like
#,Day, Date and time, minutes
1,Tue, 1/1/2012 1:00 am, 60
2,Wed, 1/2/2012 3:00 am, 60
2,Wed, 1/2/2012 4:00 am, 60
3,Wed, 1/2/2012 2:00 pm, 60
3,Wed, 1/2/2012 2:00 pm, 60
3,Wed, 1/2/2012 2:00 pm, 60
3,Wed, 1/2/2012 2:00 pm, 10
4,Wed, 1/2/2012 5:00 pm, 30

Can u help me?

**:) Sixthsense :)** · 03-15-2013, 12:34 AM

Please attach a sample workbook with expected output for better understanding

**Serdica** · 03-15-2013, 01:08 AM

OK, see the attached Detail.xlsx

**kvsrinivasamurthy** · 03-15-2013, 05:07 AM

Pl see the attached file with macro. Macro adds a worksheet by name "Output" and transfers data from "Detail" Sheet.

**Serdica** · 03-15-2013, 05:38 PM

Detail ans date iss.xlsm This is awesome! Thank you. There is one issue with it. It is breaking down the minutes, however, if it goes over 00:00 (midnight) it adds -1380 min for some reason. I have attached and highlighted the issue area.

Can we do something to fix it?