Split Time Range into Half Hour Each Row

disyn

New Member
Joined
Apr 24, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I have a data set that look like this

NameActivityDetailStartFinish
BobCookingCooking pasta22/04/2020 9:0022/04/2020 10:00
BillReadingReading novel22/04/2020 9:3022/04/2020 10:00

And i want to split it like this

NameTimeActivityDetail
Bob22/04/2020 9:00CookingCooking pasta
Bob22/04/2020 9:30CookingCooking pasta
Bill22/04/2020 9:30ReadingReading Novel

Any excel formula idea or vba macro code idea? Thank you!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi and welcome to MrExcel.

Try this, results in G2 onwards.

VBA Code:
Sub Split_Time()
  Dim i As Long, j As Long, k As Long, hrs As Double, med As Double
  Dim a As Variant, b As Variant
  a = Range("A2:E" & Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a) * 24, 1 To 4)
  For i = 1 To UBound(a)
    hrs = Round((a(i, 5) - a(i, 4)) * 48, 0)
    med = 0
    For j = 1 To hrs
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 4) + med
      b(k, 3) = a(i, 2)
      b(k, 4) = a(i, 3)
      med = j / 48
    Next
  Next
  Range("G2").Resize(k, 4).Value = b
End Sub
 
Upvote 0
Hi and welcome to MrExcel.

Try this, results in G2 onwards.

VBA Code:
Sub Split_Time()
  Dim i As Long, j As Long, k As Long, hrs As Double, med As Double
  Dim a As Variant, b As Variant
  a = Range("A2:E" & Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a) * 24, 1 To 4)
  For i = 1 To UBound(a)
    hrs = Round((a(i, 5) - a(i, 4)) * 48, 0)
    med = 0
    For j = 1 To hrs
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 4) + med
      b(k, 3) = a(i, 2)
      b(k, 4) = a(i, 3)
      med = j / 48
    Next
  Next
  Range("G2").Resize(k, 4).Value = b
End Sub
Thanks for the code, but when i tried it, there was an error message stated type mismatch. Could you please help me to analyze the error?
 
Upvote 0
You can put examples of your data but using XL2BB tool to see what columns the data is in.

Dante Amor
ABCDEFGHIJ
1NameActivityDetailStartFinish
2BobCookingCooking pasta22/04/2020 09:0022/04/2020 10:00Bob22/04/2020 09:00CookingCooking pasta
3BillReadingReading novel22/04/2020 09:3022/04/2020 10:00Bob22/04/2020 09:30CookingCooking pasta
4Bill22/04/2020 09:30ReadingReading novel
Hoja3
 
Upvote 0
You can put examples of your data but using XL2BB tool to see what columns the data is in.

Dante Amor
ABCDEFGHIJ
1NameActivityDetailStartFinish
2BobCookingCooking pasta22/04/2020 09:0022/04/2020 10:00Bob22/04/2020 09:00CookingCooking pasta
3BillReadingReading novel22/04/2020 09:3022/04/2020 10:00Bob22/04/2020 09:30CookingCooking pasta
4Bill22/04/2020 09:30ReadingReading novel
Hoja3
I already put data in right column just like in your example but it stated type mismatch, any idea?
 
Upvote 0
In columns D and E you must have dates. If you have text you must convert them to dates, as shown in my example. That is why the error.
 
Upvote 0
I
In columns D and E you must have dates. If you have text you must convert them to dates, as shown in my example. That is why the error.
I have set the cell to dates but it was still error. I have no idea why this is happening :(
 
Upvote 0
I
In columns D and E you must have dates. If you have text you must convert them to dates, as shown in my example. That is why the error.
I have set the cell to dates but it was still error. I have no idea why this is happening :(
 
Upvote 0
Activity Management.xlsm
GHIJKLMNOPQ
1Activity NoUserActivityDescriptionDayMonthYearStart TimeEnd TimeDate StartDate End
2A-1Staff 3testtest5620209:0010:0005/06/2020 09:0005/06/2020 10:00
3A-2Staff 4test1test14820208:309:3004/08/2020 08:3004/08/2020 09:30
Activity Data
Cell Formulas
RangeFormula
P2:P3P2=TEXT(DATE(M2,L2,K2),"dd/mm/yyyy ")&TEXT([@[Start Time]],"hh:mm")
Q2:Q3Q2=TEXT(DATE([@Year],[@Month],[@Day]),"dd/mm/yyyy ")&TEXT([@[End Time]],"hh:mm")


this is the table, actually i modify the start date and end date so it contains formula. could you please help me to design the macro for this condition?
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

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
Back
Top