Split Time Range into Half Hour Each Row

[disyn]

I have a data set that look like this

Name	Activity	Detail	Start	Finish
Bob	Cooking	Cooking pasta	22/04/2020 9:00	22/04/2020 10:00
Bill	Reading	Reading novel	22/04/2020 9:30	22/04/2020 10:00

And i want to split it like this

Name	Time	Activity	Detail
Bob	22/04/2020 9:00	Cooking	Cooking pasta
Bob	22/04/2020 9:30	Cooking	Cooking pasta
Bill	22/04/2020 9:30	Reading	Reading Novel

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

 

[DanteAmor]

Hi and welcome to MrExcel.

Try this, results in G2 onwards.

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

 

[disyn]

Hi and welcome to MrExcel.

Try this, results in G2 onwards.

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?

 

[DanteAmor]

You can put examples of your data but using XL2BB tool to see what columns the data is in.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Name	Activity	Detail	Start	Finish
2	Bob	Cooking	Cooking pasta	22/04/2020 09:00	22/04/2020 10:00		Bob	22/04/2020 09:00	Cooking	Cooking pasta
3	Bill	Reading	Reading novel	22/04/2020 09:30	22/04/2020 10:00		Bob	22/04/2020 09:30	Cooking	Cooking pasta
4							Bill	22/04/2020 09:30	Reading	Reading novel
Hoja3

 

[disyn]

You can put examples of your data but using XL2BB tool to see what columns the data is in.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Name	Activity	Detail	Start	Finish
2	Bob	Cooking	Cooking pasta	22/04/2020 09:00	22/04/2020 10:00		Bob	22/04/2020 09:00	Cooking	Cooking pasta
3	Bill	Reading	Reading novel	22/04/2020 09:30	22/04/2020 10:00		Bob	22/04/2020 09:30	Cooking	Cooking pasta
4							Bill	22/04/2020 09:30	Reading	Reading novel
Hoja3

I already put data in right column just like in your example but it stated type mismatch, any idea?

 

[DanteAmor]

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.

 

[disyn]

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

 

[disyn]

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

 

[DanteAmor]

You can put examples of your data but using XL2BB tool to see what columns the data is i

Please put your sample data here.

 

[disyn]

Activity Management.xlsm
	G	H	I	J	K	L	M	N	O	P	Q
1	Activity No	User	Activity	Description	Day	Month	Year	Start Time	End Time	Date Start	Date End
2	A-1	Staff 3	test	test	5	6	2020	9:00	10:00	05/06/2020 09:00	05/06/2020 10:00
3	A-2	Staff 4	test1	test1	4	8	2020	8:30	9:30	04/08/2020 08:30	04/08/2020 09:30
Activity Data

Cell Formulas
Range	Formula
P2:P3	P2	=TEXT(DATE(M2,L2,K2),"dd/mm/yyyy ")&TEXT([@[Start Time]],"hh:mm")
Q2:Q3	Q2	=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?