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!

 

[disyn]

Replace formulas

varios 27abr2020.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	09:00:00 a.m.	10:00:00 a.m.	05/06/2020 09:00	05/06/2020 10:00
3	A-2	Staff 4	test1	test1	4	8	2020	08:30:00 a.m.	09:30:00 a.m.	04/08/2020 08:30	04/08/2020 09:30
Sheet6

Cell Formulas
Range	Formula
P2:P3	P2	=(TEXT(DATE([Year],[Month],[Day]),"dd/mm/yyyy ")&TEXT([Start Time],"hh:mm"))+0
Q2:Q3	Q2	=(TEXT(DATE([Year],[Month],[Day]),"dd/mm/yyyy ")&TEXT([End Time],"hh:mm"))+0

Use this code in userform:

Private Sub CommandButton3_Click() 'submit button
  Dim indeks As Integer
  With Sheet6.ListObjects("Tabel_Activity")
    .ListRows.Add AlwaysInsert:=True
    indeks = .ListRows.Count + 1
    .Range.Cells(indeks, 1) = "A-" & indeks
    .Range.Cells(indeks, 2) = ComboBox1.Value
    .Range.Cells(indeks, 3) = TextBox1.Value
    .Range.Cells(indeks, 4) = TextBox2.Value
    .Range.Cells(indeks, 5) = Val(ComboBox2.Value)  'day
    .Range.Cells(indeks, 6) = Val(ComboBox3.Value)  'month
    .Range.Cells(indeks, 7) = Val(ComboBox4.Value)  'year
    .Range.Cells(indeks, 8) = ComboBox5.Value       'start
    .Range.Cells(indeks, 9) = ComboBox8.Value       'end
  End With
  Unload Me
End Sub

Try again

thank you so much, it works. anyway, i want to ask additional question, how should i format 12:00 pm o'clock for the combobox? because it appears as 12:05 am instead

 

[DanteAmor]

Something like this:

Private Sub UserForm_Activate()
  Dim i
  For i = TimeValue("00:00") To TimeValue("23:31") Step TimeValue("00:30")
    ComboBox5.AddItem Format(i, "hh:mm AM/PM")
  Next
End Sub

 

[disyn]

Something like this:

Private Sub UserForm_Activate()
  Dim i
  For i = TimeValue("00:00") To TimeValue("23:31") Step TimeValue("00:30")
    ComboBox5.AddItem Format(i, "hh:mm AM/PM")
  Next
End Sub

i tried that but it show error 70 permission denied, any idea?

 

[disyn]

Replace formulas

varios 27abr2020.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	09:00:00 a.m.	10:00:00 a.m.	05/06/2020 09:00	05/06/2020 10:00
3	A-2	Staff 4	test1	test1	4	8	2020	08:30:00 a.m.	09:30:00 a.m.	04/08/2020 08:30	04/08/2020 09:30
Sheet6

Cell Formulas
Range	Formula
P2:P3	P2	=(TEXT(DATE([Year],[Month],[Day]),"dd/mm/yyyy ")&TEXT([Start Time],"hh:mm"))+0
Q2:Q3	Q2	=(TEXT(DATE([Year],[Month],[Day]),"dd/mm/yyyy ")&TEXT([End Time],"hh:mm"))+0

Use this code in userform:

Private Sub CommandButton3_Click() 'submit button
  Dim indeks As Integer
  With Sheet6.ListObjects("Tabel_Activity")
    .ListRows.Add AlwaysInsert:=True
    indeks = .ListRows.Count + 1
    .Range.Cells(indeks, 1) = "A-" & indeks
    .Range.Cells(indeks, 2) = ComboBox1.Value
    .Range.Cells(indeks, 3) = TextBox1.Value
    .Range.Cells(indeks, 4) = TextBox2.Value
    .Range.Cells(indeks, 5) = Val(ComboBox2.Value)  'day
    .Range.Cells(indeks, 6) = Val(ComboBox3.Value)  'month
    .Range.Cells(indeks, 7) = Val(ComboBox4.Value)  'year
    .Range.Cells(indeks, 8) = ComboBox5.Value       'start
    .Range.Cells(indeks, 9) = ComboBox8.Value       'end
  End With
  Unload Me
End Sub

Try again

anyway it's a bit weird, i use this coding and also formula you have given for the table. it runs well but the content of activity number (A-indeks) always skips one number. For example, if the last row is A-4, when i add new activity from userform, the next activity number is A-6. I tried to modify the "A-" & indeks part become "A-" & indeks-1. The activity number comes in sequence but the formula to in P and Q column show #VALUE error. Could you please help me to solve this little error?

 

[DanteAmor]

i tried that but it show error 70 permission denied, any idea?

How do you load combo5 and combo8? with Rowsource?
You can put here all the code of your userform.
___________________________________________________________________________

For example, if the last row is A-4, when i add new activity from userform, the next activity number is A-6.

Check that you have no blank rows in the table.

 

[DanteAmor]

I have no problems, neither with the table, nor with the formulas, nor with the consecutive one, nor with the Split.

Do you need to have the data in a Table?

It is more practical if you had the data in a normal range of cells. But it is your information.

 

[disyn]

I have no problems, neither with the table, nor with the formulas, nor with the consecutive one, nor with the Split.



View attachment 12526
Do you need to have the data in a Table?

It is more practical if you had the data in a normal range of cells. But it is your information.

i have tried again and finally there is no problem with the table and splitting. but i still encounter error with 12:00. I use this rowsource for combobox 5. the 12:00 o'clock in the table stated 12:00 PM but every time i choose 12:00 in combobox, it shows 12:05 instead.

8:30​

9:00​

9:30​

10:00​

10:30​

11:00​

11:30​

12:00​

12:30​

13:00​

13:30​

14:00​

14:30​

15:00​

15:30​

16:00​

16:30​

17:00​

17:30​

 

[DanteAmor]

How do you load combo5 and combo8? with Rowsource?

 

[disyn]

i inputted the rowsource on combobox properties, like this one

 

[disyn]

I have no problems, neither with the table, nor with the formulas, nor with the consecutive one, nor with the Split.



View attachment 12526
Do you need to have the data in a Table?

It is more practical if you had the data in a normal range of cells. But it is your information.

Anyway i have a question, is it possible if i delete a row in the initial table via the userform and then the row in result table also be deleted?