Possible to pull apart 1st section of string for date & time

[Bond00]

So people type notes in short hand, and I'd like to be able to have a button linked to vba to auto pull apart the first date and time part of the text and put them into the proper cells shown below.
So this list in Col D will go down to an unknown row # so maybe use LRow = ws2.Range("B138").End(xlUp).Row to get the bottom row to work with. (this is in worksheet2)
Example:

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Cus	Ticket	Description	Date	Start Time	AM/PM	End Time	AM/PM
2				10/8 2-530pm Work on system checks, fixed any errors found	8-Oct	2:00	PM	5:30	PM
3				10/9 11am-230pm Work on system repairs and firmware upgrades	9-Oct	11:00	AM	2:30	PM
4				10/9 230-5pm Work on reviews	9-Oct	2:30	PM	5:00	PM
5				10/10 930-1130am Worked on installing local program accounts and called service to get software downloads, after install setup attached its database to main server.	10-Oct	9:30	AM	11:30	AM
6				10/10 1130-3pm Review all system projects and collect data	10-Oct	11:30	AM	3:00	PM
Sheet1

So if they type 10/8 2-530pm put the date into col E and start time being 2pm into col F and PM into col G and 530 into col H and PM into col I
Now it can be assumed (almost always) that if the first # doesn't have a AM or PM with it that its the same as the 2nd # in this case above its 2-530pm so 2 is also PM by assumption.

There is occasional times when its like 10/10 1130-3pm and the 11:30 is assumed AM in this case since they didn't work from 1130pm yesterday until 3pm today. So maybe a rule that if the math is over 12 hrs then assume the correct time from that. in this case assume its AM since if it was 11:30PM-3PM it would be over 12 hrs span.
If this rule is ever broken for some reason then its fine we just correct it manually since it will almost never happen.

Also when the button is pressed and the date and time is put into the proper cells remove the first part and place the rest of the text back into col D (without the date and time in front)

thanks for taking a look at this!

 

[Peter_SSs]

This may not be exactly what you are after but lets see if it is close.
Test with a copy of your data as it over-writes existing data.

Sub Date_Time()
  Dim a As Variant, b As Variant, Bits As Variant
  Dim i As Long
  
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 6)
    For i = 1 To UBound(a)
      Bits = Split(Replace(Replace(a(i, 1), "-", " "), "/", " "), , 5)
      b(i, 1) = Bits(4)
      b(i, 2) = DateSerial(Year(Date), Bits(0), Bits(1))
      b(i, 3) = Val(Bits(2))
      If b(i, 3) < 12 Then
        b(i, 3) = TimeSerial(b(i, 3), 0, 0)
      Else
        b(i, 3) = TimeSerial(Left(b(i, 3), Len(b(i, 3)) - 2), Right(b(i, 3), 2), 0)
      End If
      b(i, 4) = UCase(Mid(a(i, 1), InStr(1, a(i, 1), "m", vbTextCompare) - 1, 2))
      b(i, 5) = Val(Bits(3))
      If b(i, 5) < 12 Then
        b(i, 5) = TimeSerial(b(i, 5), 0, 0)
      Else
        b(i, 5) = TimeSerial(Left(b(i, 5), Len(b(i, 5)) - 2), Right(b(i, 5), 2), 0)
      End If
      If b(i, 3) > b(i, 5) Then b(i, 4) = "AM"
      b(i, 6) = UCase(Right(Bits(3), 2))
    Next i
    With .Resize(, 6)
      .Value = b
      .Columns(2).NumberFormat = "d-mmm"
      .Columns(3).NumberFormat = "h:mm"
      .Columns(5).NumberFormat = "h:mm"
    End With
  End With
End Sub

 

[Bond00]

This may not be exactly what you are after but lets see if it is close.
Test with a copy of your data as it over-writes existing data.

Sub Date_Time()
  Dim a As Variant, b As Variant, Bits As Variant
  Dim i As Long
 
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 6)
    For i = 1 To UBound(a)
      Bits = Split(Replace(Replace(a(i, 1), "-", " "), "/", " "), , 5)
      b(i, 1) = Bits(4)
      b(i, 2) = DateSerial(Year(Date), Bits(0), Bits(1))
      b(i, 3) = Val(Bits(2))
      If b(i, 3) < 12 Then
        b(i, 3) = TimeSerial(b(i, 3), 0, 0)
      Else
        b(i, 3) = TimeSerial(Left(b(i, 3), Len(b(i, 3)) - 2), Right(b(i, 3), 2), 0)
      End If
      b(i, 4) = UCase(Mid(a(i, 1), InStr(1, a(i, 1), "m", vbTextCompare) - 1, 2))
      b(i, 5) = Val(Bits(3))
      If b(i, 5) < 12 Then
        b(i, 5) = TimeSerial(b(i, 5), 0, 0)
      Else
        b(i, 5) = TimeSerial(Left(b(i, 5), Len(b(i, 5)) - 2), Right(b(i, 5), 2), 0)
      End If
      If b(i, 3) > b(i, 5) Then b(i, 4) = "AM"
      b(i, 6) = UCase(Right(Bits(3), 2))
    Next i
    With .Resize(, 6)
      .Value = b
      .Columns(2).NumberFormat = "d-mmm"
      .Columns(3).NumberFormat = "h:mm"
      .Columns(5).NumberFormat = "h:mm"
    End With
  End With
End Sub

well i tested it and it seems to work spot on, so thanks a lot for that!
Would you mind adding in a few comments so i follow what its doing in case i need to ever alter it someday.

 

[Bond00]

Oh also can you put a check in the front in case they push the button twice so it doesnt error, like if the first character isnt a # then skip to the end.

 

[Peter_SSs]

well i tested it and it seems to work spot on, so thanks a lot for that!

You're welcome. Glad it seems to work. Thanks for the follow-up.

Would you mind adding in a few comments so i follow what its doing in case i need to ever alter it someday.

Rather than add comments in the code I think I will work through one example and hopefully you can match the step to the parts of the code.
The example I will use is
10/9 230-5pm Work on reviews

Replace each "-" and each "/" with a space character
10 9 230 5pm Work on reviews

Now break it apart at the space characters, but stop after you have 5 results, and put the results into a zero-based string array called Bits. The results are
Bits(0) = "10"
Bits(1) = "9"
Bits(2) = "230"
Bits(3) = "5pm"
Bits(4) = "Work on reviews"

The results are put into the horizontal 1-based array b. The example data comes from the 3rd row of sample data & goes into the 3rd row of results hence the 3 at the start of each array element below
b(3,1) = Bits(4) = "Work on reviews"

b(3,2) = The date created using the current year, the month from Bits(0) = 10 and day from Bits(1) = 9 which gives 9 October 2024

b(3,3) first takes the number part of Bits(2) = 230
(but Bits(2) could have been say 230pm and the number part would still be 230)
If this number is <12 (maybe it should be <=12) then b(3,3) is changed to a time with that many hours and 0 minutes and 0 seconds.
If the number is >12 (greater than 3 digits) then it is turned into a time with hours being the left 1 or 2 digits depending on whether the starting number is 3 or 4 digits and minutes being the right 2 digits with 0 seconds. So ..
b(3,3) = 2:30

b(3,4)) looks in the original data (10/9 230-5pm Work on reviews) for an "m" since all your times have at least one "am" or "pm" & takes that letter and the one before it and make upper case. So
b(3,4) = "PM"

b(3,5) works exactly like b(3,3) above but starts with Bits(3) = "5pm". So after the manipulations
b(3,5) = 5:00

There is now a check. If b(3,3) > b(3,5) then you must have that 'start morning - finish afternoon' situation you described so b(3,4) would be made "AM". It may already have been "AM" but this ensures it. In our case 2:20 > 5:00 is FALSE so b(3,4) is left alone (still "PM")

b(3,6) now takes the an/pm from the right of Bits(3) since that part always has one or the other
b(3,6) = "PM"

All the rows are processed like this then results written to columns D:I and relevant columns formatted as date or time.

In relation to your post 4 request I have added a check for the first character of the first data row. If that is a digit then all rows are processed otherwise nothing is done.

Sub Date_Time_v2()
  Dim a As Variant, b As Variant, Bits As Variant
  Dim i As Long
 
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    If IsNumeric(Left(a(1, 1), 1)) Then
      ReDim b(1 To UBound(a), 1 To 6)
      For i = 1 To UBound(a)
        Bits = Split(Replace(Replace(a(i, 1), "-", " "), "/", " "), , 5)
        b(i, 1) = Bits(4)
        b(i, 2) = DateSerial(Year(Date), Bits(0), Bits(1))
        b(i, 3) = Val(Bits(2))
        If b(i, 3) < 12 Then
          b(i, 3) = TimeSerial(b(i, 3), 0, 0)
        Else
          b(i, 3) = TimeSerial(Left(b(i, 3), Len(b(i, 3)) - 2), Right(b(i, 3), 2), 0)
        End If
        b(i, 4) = UCase(Mid(a(i, 1), InStr(1, a(i, 1), "m", vbTextCompare) - 1, 2))
        b(i, 5) = Val(Bits(3))
        If b(i, 5) < 12 Then
          b(i, 5) = TimeSerial(b(i, 5), 0, 0)
        Else
          b(i, 5) = TimeSerial(Left(b(i, 5), Len(b(i, 5)) - 2), Right(b(i, 5), 2), 0)
        End If
        If b(i, 3) > b(i, 5) Then b(i, 4) = "AM"
        b(i, 6) = UCase(Right(Bits(3), 2))
      Next i
      With .Resize(, 6)
        .Value = b
        .Columns(2).NumberFormat = "d-mmm"
        .Columns(3).NumberFormat = "h:mm"
        .Columns(5).NumberFormat = "h:mm"
      End With
    End If
  End With
End Sub

 

[Bond00]

ah i just noticed one issue. it sometimes assumes AM like if someone worked from 2pm to 12am or 1am which is unlikely but could happen on rare occasion.
I guess is it possible to fix that if they type the pm and am with both numbers to always default to take that without added checks?
so if its typed 4am-11pm even though thats 19 hours and doesn't make sense if its typed and specified that way then take it for what it is. thats probably the safest to do.
I show it below highlighted yellow

Book1
	A	B	C	D	E	F	G	H	I
15	ID	Cus	Ticket	Description	Date	Start Time	AM/PM	End Time	AM/PM
16				10/8 2-530pm Work on system checks, fixed any errors found	8-Oct	2:00	PM	5:30	PM
17				10/9 11am-230pm Work on system repairs and firmware upgrades	9-Oct	11:00	AM	2:30	PM
18				10/9 230-5pm Work on reviews	9-Oct	2:30	PM	5:00	PM
19				10/10 930-1130am Worked on installing local program accounts and called service to get software downloads, after install setup attached its database to mainserve	10-Oct	9:30	AM	11:30	AM
20				10/10 1130-3pm Review all system projects and collect data	10-Oct	11:30	AM	3:00	PM
21				10/8 8-530pm Work on system checks, fixed any errors found	8-Oct	8:00	AM	5:30	PM
22				10/9 11-230pm Work on system repairs and firmware upgrades	9-Oct	11:00	AM	2:30	PM
23				10/9 230-1am Work on reviews	9-Oct	2:30	AM	1:00	AM
24				10/9 230am-1am Work on reviews	9-Oct	2:30	AM	1:00	AM
25				10/9 230pm-1am Work on reviews	9-Oct	2:30	AM	1:00	AM
26				10/10 630-1130am Worked on installing local program accounts and called service to	10-Oct	6:30	AM	11:30	AM
27				10/10 1045-8pm Review all system projects and collect data	10-Oct	10:45	AM	8:00	PM
28				10/10 230pm-1am Worked on installing local program accounts and called service to	10-Oct	2:30	AM	1:00	AM
29				10/10 4-2am Review all system projects and collect data	10-Oct	4:00	AM	2:00	AM
Sheet1

I was reading your walkthrough above and i assume it may now need a possible 6 results if you take the first am/pm as input now right? But it will sometimes be 5 and sometimes be 6 results then

 

[Bond00]

hmm never mind, it probably wouldn't need 6 results because if it has the am/pm on the first time AND the 2nd time then you probably can skip some of the checks you do right?
like this
"If this number is <12 (maybe it should be <=12) then b(3,3) is changed to a time with that many hours and 0 minutes and 0 seconds.
If the number is >12 (greater than 3 digits) then it is turned into a time with hours being the left 1 or 2 digits depending on whether the starting number is 3 or 4 digits and minutes being the right 2 digits with 0 seconds. So .."

but it would still have to go through the rest of your logic in the code so yeah i guess not sure the best route..

 

[Peter_SSs]

I'm not sure what you mean by 5 or 6 results and I have not tested/checked this much at all but see how it goes. Basically if 1st time > 2nd time then first AM/PM is "opposite" of 2nd AM/PM.

Sub Date_Time_v3()
  Dim a As Variant, b As Variant, Bits As Variant
  Dim i As Long
  
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    If IsNumeric(Left(a(1, 1), 1)) Then
      ReDim b(1 To UBound(a), 1 To 6)
      For i = 1 To UBound(a)
        Bits = Split(Replace(Replace(a(i, 1), "-", " "), "/", " "), , 5)
        b(i, 1) = Bits(4)
        b(i, 2) = DateSerial(Year(Date), Bits(0), Bits(1))
        b(i, 3) = Val(Bits(2))
        If b(i, 3) <= 12 Then
          b(i, 3) = TimeSerial(b(i, 3), 0, 0)
        Else
          b(i, 3) = TimeSerial(Left(b(i, 3), Len(b(i, 3)) - 2), Right(b(i, 3), 2), 0)
        End If
        b(i, 4) = UCase(Mid(a(i, 1), InStr(1, a(i, 1), "m", vbTextCompare) - 1, 2))
        b(i, 5) = Val(Bits(3))
        If b(i, 5) <= 12 Then
          b(i, 5) = TimeSerial(b(i, 5), 0, 0)
        Else
          b(i, 5) = TimeSerial(Left(b(i, 5), Len(b(i, 5)) - 2), Right(b(i, 5), 2), 0)
        End If
        b(i, 6) = UCase(Right(Bits(3), 2))
        If b(i, 3) > b(i, 5) Then b(i, 4) = IIf(b(i, 6) = "PM", "AM", "PM")
      Next i
      With .Resize(, 6)
        .Value = b
        .Columns(2).NumberFormat = "d-mmm"
        .Columns(3).NumberFormat = "h:mm"
        .Columns(5).NumberFormat = "h:mm"
      End With
    End If
  End With
End Sub

 

[Bond00]

I'm not sure what you mean by 5 or 6 results and I have not tested/checked this much at all but see how it goes. Basically if 1st time > 2nd time then first AM/PM is "opposite" of 2nd AM/PM.

Sub Date_Time_v3()
  Dim a As Variant, b As Variant, Bits As Variant
  Dim i As Long
 
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    If IsNumeric(Left(a(1, 1), 1)) Then
      ReDim b(1 To UBound(a), 1 To 6)
      For i = 1 To UBound(a)
        Bits = Split(Replace(Replace(a(i, 1), "-", " "), "/", " "), , 5)
        b(i, 1) = Bits(4)
        b(i, 2) = DateSerial(Year(Date), Bits(0), Bits(1))
        b(i, 3) = Val(Bits(2))
        If b(i, 3) <= 12 Then
          b(i, 3) = TimeSerial(b(i, 3), 0, 0)
        Else
          b(i, 3) = TimeSerial(Left(b(i, 3), Len(b(i, 3)) - 2), Right(b(i, 3), 2), 0)
        End If
        b(i, 4) = UCase(Mid(a(i, 1), InStr(1, a(i, 1), "m", vbTextCompare) - 1, 2))
        b(i, 5) = Val(Bits(3))
        If b(i, 5) <= 12 Then
          b(i, 5) = TimeSerial(b(i, 5), 0, 0)
        Else
          b(i, 5) = TimeSerial(Left(b(i, 5), Len(b(i, 5)) - 2), Right(b(i, 5), 2), 0)
        End If
        b(i, 6) = UCase(Right(Bits(3), 2))
        If b(i, 3) > b(i, 5) Then b(i, 4) = IIf(b(i, 6) = "PM", "AM", "PM")
      Next i
      With .Resize(, 6)
        .Value = b
        .Columns(2).NumberFormat = "d-mmm"
        .Columns(3).NumberFormat = "h:mm"
        .Columns(5).NumberFormat = "h:mm"
      End With
    End If
  End With
End Sub

I just meant you may need 6 results (bits0-5) if you store the first am or pm as well but whatever you did it worked good.
It only had 1 that wasnt right but that situation would never happen anyway. no one will start work at 230am and work till that next day at 1am in the morning. So as far as i'm concerned its good!

Book1
	A	B	C	D	E	F	G	H	I
15	ID	Cus	Ticket	Description	Date	Start Time	AM/PM	End Time	AM/PM
16				10/8 2-530pm Work on system checks, fixed any errors found	8-Oct	2:00	PM	5:30	PM
17				10/9 11am-230pm Work on system repairs and firmware upgrades	9-Oct	11:00	AM	2:30	PM
18				10/9 230-5pm Work on reviews	9-Oct	2:30	PM	5:00	PM
19				10/10 930-1130am Worked on installing local program accounts and called service to get software downloads, after install setup attached its database to mainserve	10-Oct	9:30	AM	11:30	AM
20				10/10 1130-3pm Review all system projects and collect data	10-Oct	11:30	AM	3:00	PM
21				10/8 8-530pm Work on system checks, fixed any errors found	8-Oct	8:00	AM	5:30	PM
22				10/9 11-230pm Work on system repairs and firmware upgrades	9-Oct	11:00	AM	2:30	PM
23				10/9 230-1am Work on reviews	9-Oct	2:30	PM	1:00	AM
24				10/9 230am-1am Work on reviews	9-Oct	2:30	PM	1:00	AM
25				10/9 230pm-1am Work on reviews	9-Oct	2:30	PM	1:00	AM
26				10/10 630-1130am Worked on installing local program accounts and called service to	10-Oct	6:30	AM	11:30	AM
27				10/10 1045-8pm Review all system projects and collect data	10-Oct	10:45	AM	8:00	PM
28				10/10 230pm-1am Worked on installing local program accounts and called service to	10-Oct	2:30	PM	1:00	AM
29				10/10 4-2am Review all system projects and collect data	10-Oct	4:00	PM	2:00	AM
Sheet1

Thanks a lot for your help with this!

 

[Peter_SSs]

I just meant you may need 6 results (bits0-5) if you store the first am or pm as well

Ah, I see. No, it didn't need an extra part as both times had an am/pm they were already collected and used (like in cell D3 of your original post)

It only had 1 that wasnt right but that situation would never happen anyway. no one will start work at 230am and work till that next day at 1am in the morning. So as far as i'm concerned its good!

I'm pretty sure the code will work unless a work period exceeds 12 hours, which I assumed unlikely.