How to split rows by date and time?

[wilkoaid]

I already posted yesterday in the Excel Forum with this problem, but have not received a reply yet so I hope someone here might have some ideas on how to go about solving this.

I have a large set of data for the dates and times when a vehicle is parked.

e.g. the start date/time is 07/10/2016 at 18:00 (with time on a separate column) and end date/time is 08/10/2016 at 08:30.

I would like to split an entry like this into multiple rows so that there is a new row for each date.
e.g. first row is 07/10/2016 18:00 08/10/2016 00:00
second row is 08/10/2016 00:00 08/10/2016 08:30

Is this possible with some formula or macro? It is simply not feasible to do it all by hand, there are too many entries.
I have attached an example file with the format of data I have now, and the desired output data.

Many thanks in advance.

 

[wilkoaid]

Sorry, apologies I didn't actually attach anything. Here is the link to the Excel Forum post where you can download an example file.

 

[wilkoaid]

https://www.excelforum.com/excel-general/1200664-how-to-split-rows-by-date-and-time.html

 

[wideboydixon]

Based on your example tables:

Public Sub SplitMultipleDates()

Dim lastRow As Long
Dim thisRow As Long

Application.ScreenUpdating = False

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
thisRow = 2
Do Until thisRow > lastRow
    If Cells(thisRow, 1).Value < Cells(thisRow, 3).Value Then
        Rows(thisRow).Copy
        Rows(thisRow + 1).Insert xlShiftDown
        Cells(thisRow, 3).Value = Cells(thisRow, 1).Value
        Cells(thisRow, 4).Value = 0
        Cells(thisRow + 1, 1).Value = Cells(thisRow, 1).Value + 1
        Cells(thisRow + 1, 2).Value = 0
        lastRow = lastRow + 1
    End If
    thisRow = thisRow + 1
Loop

Application.ScreenUpdating = True

End Sub

WBD

 

[wilkoaid]

Wow that works perfectly! Thank you so much!