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!

 

[Bond00]

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)



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

Yeah its good how it is now, that one post i made above is unlikely to ever happen.

Also i get the decimal # of time from those rows, is this the best way/most efficient formula for that? this is in row16

=IF(F16<>"",SUM(MOD(TIMEVALUE(TEXT(H16, "h:mm")&" "&I16)-TIMEVALUE(TEXT(F16, "h:mm")&" "&G16),1)*1440)/60,"")

 

[Bond00]

ah one other little issue i found, it doesn't handle 12pm noon correctly.

Book1
	D	E	F	G	H	I
31	11/22 12-4pm fixed stuff	22-Nov	12:00	AM	4:00	PM
32	11/22 1215-4pm fixed stuff	22-Nov	12:15	AM	4:00	PM
33	11/22 1230-4pm fixed stuff	22-Nov	12:30	AM	4:00	PM
Sheet1

 

[Peter_SSs]

Also i get the decimal # of time from those rows, is this the best way/most efficient formula for that? this is in row16

Hmm, I wondered from early on about the wisdom of putting the AM/PM in a separate column, but it was what you had asked for. Do you really need that?
If the Start/End times could be displayed as below, many of the calculations, including the hours calculation would be easier. Also, why not have the macro calculate those hours. You did not specify where that formula was so below I have put those results in col H. If they need to be in a different column then that could be arranged.

To help check, I have included my sample data in column C. So before the code, that same data was also in column D.
Would this be any use?

Sub Date_Time_v4()
  Dim a As Variant, b As Variant, Bits As Variant, t As Variant
  Dim i As Long
  Dim ampm1 As String, ampm2 As String
  
  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 5)
      For i = 1 To UBound(a)
        Bits = Split(Replace(Replace(a(i, 1), "-", " "), "/", " "), , 5)
        ampm1 = ""
        b(i, 1) = Bits(4)
        b(i, 2) = DateSerial(Year(Date), Bits(0), Bits(1))
        ampm2 = UCase(Right(Bits(3), 2))
        t = Replace(Bits(3), ampm2, "", , , 1)
        b(i, 4) = TimeValue(IIf(Len(t) < 3, t, Format(t, "00:00")) & ampm2)
        If UCase(Right(Bits(2), 1)) = "M" Then ampm1 = UCase(Right(Bits(2), 2))
        t = Replace(Bits(2), ampm1, "", , , 1)
        b(i, 3) = TimeValue(IIf(Len(t) < 3, t, Format(t, "00:00")) & IIf(ampm1 = "", ampm2, ampm1))
        If b(i, 3) > b(i, 4) And ampm1 = "" Then b(i, 3) = b(i, 3) + IIf(ampm2 = "AM", 0.5, -0.5)
        b(i, 5) = 24 * IIf(b(i, 3) > b(i, 4), b(i, 3) - b(i, 4), b(i, 4) - b(i, 3))
        If b(i, 5) > 12 Then b(i, 5) = 24 - b(i, 5)
      Next i
      With .Resize(, 5)
        .Value = b
        .Columns(2).NumberFormat = "d-mmm"
        .Columns(3).Resize(, 2).NumberFormat = "h:mm AM/PM"
        .Columns(5).NumberFormat = "0.00"
      End With
    End If
  End With
End Sub

Bond00.xlsm
	C	D	E	F	G	H
1	OriginalDescription	Description	Date	Start Time	End Time	Hrs
2	10/8 2-530pm Work on system checks, fixed any errors found	Work on system checks, fixed any errors found	8-Oct	2:00 PM	5:30 PM	3.50
3	10/9 11am-230pm Work on system repairs and firmware upgrades	Work on system repairs and firmware upgrades	9-Oct	11:00 AM	2:30 PM	3.50
4	10/9 230-5pm Work on reviews	Work on reviews	9-Oct	2:30 PM	5:00 PM	2.50
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.	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	2.00
6	10/10 1130-3pm Review all system projects and collect data	Review all system projects and collect data	10-Oct	11:30 AM	3:00 PM	3.50
7	10/8 2-530pm Work on system checks, fixed any errors found	Work on system checks, fixed any errors found	8-Oct	2:00 PM	5:30 PM	3.50
8	10/9 11am-230pm Work on system repairs and firmware upgrades	Work on system repairs and firmware upgrades	9-Oct	11:00 AM	2:30 PM	3.50
9	10/9 230-5pm Work on reviews	Work on reviews	9-Oct	2:30 PM	5:00 PM	2.50
10	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	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	2.00
11	10/10 1130-3pm Review all system projects and collect data	Review all system projects and collect data	10-Oct	11:30 AM	3:00 PM	3.50
12	10/8 8-530pm Work on system checks, fixed any errors found	Work on system checks, fixed any errors found	8-Oct	8:00 AM	5:30 PM	9.50
13	10/9 11-230pm Work on system repairs and firmware upgrades	Work on system repairs and firmware upgrades	9-Oct	11:00 AM	2:30 PM	3.50
14	10/9 230-1am Work on reviews	Work on reviews	9-Oct	2:30 PM	1:00 AM	10.50
15	10/9 230pm-1am Work on reviews	Work on reviews	9-Oct	2:30 PM	1:00 AM	10.50
16	10/10 630-1130am Worked on installing local program accounts and called service to	Worked on installing local program accounts and called service to	10-Oct	6:30 AM	11:30 AM	5.00
17	10/10 1045-8pm Review all system projects and collect data	Review all system projects and collect data	10-Oct	10:45 AM	8:00 PM	9.25
18	10/10 230pm-1am Worked on installing local program accounts and called service to	Worked on installing local program accounts and called service to	10-Oct	2:30 PM	1:00 AM	10.50
19	10/10 4-2am Review all system projects and collect data	Review all system projects and collect data	10-Oct	4:00 PM	2:00 AM	10.00
20	11/22 12-4pm fixed stuff	fixed stuff	22-Nov	12:00 PM	4:00 PM	4.00
21	11/22 1215-4pm fixed stuff	fixed stuff	22-Nov	12:15 PM	4:00 PM	3.75
22	11/22 1230-4pm fixed stuff	fixed stuff	22-Nov	12:30 PM	4:00 PM	3.50
23	11/22 1230-4am fixed stuff	fixed stuff	22-Nov	12:30 AM	4:00 AM	3.50
24	11/22 12-4am fixed stuff	fixed stuff	22-Nov	12:00 AM	4:00 AM	4.00
Sheet4

 

[Bond00]

Hmm, I wondered from early on about the wisdom of putting the AM/PM in a separate column, but it was what you had asked for. Do you really need that?
If the Start/End times could be displayed as below, many of the calculations, including the hours calculation would be easier. Also, why not have the macro calculate those hours. You did not specify where that formula was so below I have put those results in col H. If they need to be in a different column then that could be arranged.

To help check, I have included my sample data in column C. So before the code, that same data was also in column D.
Would this be any use?

Sub Date_Time_v4()
  Dim a As Variant, b As Variant, Bits As Variant, t As Variant
  Dim i As Long
  Dim ampm1 As String, ampm2 As String
 
  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 5)
      For i = 1 To UBound(a)
        Bits = Split(Replace(Replace(a(i, 1), "-", " "), "/", " "), , 5)
        ampm1 = ""
        b(i, 1) = Bits(4)
        b(i, 2) = DateSerial(Year(Date), Bits(0), Bits(1))
        ampm2 = UCase(Right(Bits(3), 2))
        t = Replace(Bits(3), ampm2, "", , , 1)
        b(i, 4) = TimeValue(IIf(Len(t) < 3, t, Format(t, "00:00")) & ampm2)
        If UCase(Right(Bits(2), 1)) = "M" Then ampm1 = UCase(Right(Bits(2), 2))
        t = Replace(Bits(2), ampm1, "", , , 1)
        b(i, 3) = TimeValue(IIf(Len(t) < 3, t, Format(t, "00:00")) & IIf(ampm1 = "", ampm2, ampm1))
        If b(i, 3) > b(i, 4) And ampm1 = "" Then b(i, 3) = b(i, 3) + IIf(ampm2 = "AM", 0.5, -0.5)
        b(i, 5) = 24 * IIf(b(i, 3) > b(i, 4), b(i, 3) - b(i, 4), b(i, 4) - b(i, 3))
        If b(i, 5) > 12 Then b(i, 5) = 24 - b(i, 5)
      Next i
      With .Resize(, 5)
        .Value = b
        .Columns(2).NumberFormat = "d-mmm"
        .Columns(3).Resize(, 2).NumberFormat = "h:mm AM/PM"
        .Columns(5).NumberFormat = "0.00"
      End With
    End If
  End With
End Sub

Bond00.xlsm
	C	D	E	F	G	H
1	OriginalDescription	Description	Date	Start Time	End Time	Hrs
2	10/8 2-530pm Work on system checks, fixed any errors found	Work on system checks, fixed any errors found	8-Oct	2:00 PM	5:30 PM	3.50
3	10/9 11am-230pm Work on system repairs and firmware upgrades	Work on system repairs and firmware upgrades	9-Oct	11:00 AM	2:30 PM	3.50
4	10/9 230-5pm Work on reviews	Work on reviews	9-Oct	2:30 PM	5:00 PM	2.50
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.	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	2.00
6	10/10 1130-3pm Review all system projects and collect data	Review all system projects and collect data	10-Oct	11:30 AM	3:00 PM	3.50
7	10/8 2-530pm Work on system checks, fixed any errors found	Work on system checks, fixed any errors found	8-Oct	2:00 PM	5:30 PM	3.50
8	10/9 11am-230pm Work on system repairs and firmware upgrades	Work on system repairs and firmware upgrades	9-Oct	11:00 AM	2:30 PM	3.50
9	10/9 230-5pm Work on reviews	Work on reviews	9-Oct	2:30 PM	5:00 PM	2.50
10	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	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	2.00
11	10/10 1130-3pm Review all system projects and collect data	Review all system projects and collect data	10-Oct	11:30 AM	3:00 PM	3.50
12	10/8 8-530pm Work on system checks, fixed any errors found	Work on system checks, fixed any errors found	8-Oct	8:00 AM	5:30 PM	9.50
13	10/9 11-230pm Work on system repairs and firmware upgrades	Work on system repairs and firmware upgrades	9-Oct	11:00 AM	2:30 PM	3.50
14	10/9 230-1am Work on reviews	Work on reviews	9-Oct	2:30 PM	1:00 AM	10.50
15	10/9 230pm-1am Work on reviews	Work on reviews	9-Oct	2:30 PM	1:00 AM	10.50
16	10/10 630-1130am Worked on installing local program accounts and called service to	Worked on installing local program accounts and called service to	10-Oct	6:30 AM	11:30 AM	5.00
17	10/10 1045-8pm Review all system projects and collect data	Review all system projects and collect data	10-Oct	10:45 AM	8:00 PM	9.25
18	10/10 230pm-1am Worked on installing local program accounts and called service to	Worked on installing local program accounts and called service to	10-Oct	2:30 PM	1:00 AM	10.50
19	10/10 4-2am Review all system projects and collect data	Review all system projects and collect data	10-Oct	4:00 PM	2:00 AM	10.00
20	11/22 12-4pm fixed stuff	fixed stuff	22-Nov	12:00 PM	4:00 PM	4.00
21	11/22 1215-4pm fixed stuff	fixed stuff	22-Nov	12:15 PM	4:00 PM	3.75
22	11/22 1230-4pm fixed stuff	fixed stuff	22-Nov	12:30 PM	4:00 PM	3.50
23	11/22 1230-4am fixed stuff	fixed stuff	22-Nov	12:30 AM	4:00 AM	3.50
24	11/22 12-4am fixed stuff	fixed stuff	22-Nov	12:00 AM	4:00 AM	4.00
Sheet4

hmm yeah, i guess it isn't as important now the way it was before now with this code. There is some other things that read off of that data too from that format, but the reason it was separated like that originally was to make it easier for entering in the data. Like they could just type 1: Tab and it would enter in 1:00 and then type A or P tab and it would auto put in AM or PM etc.. so it was just faster that way than typing in the full dates and times into 1 cell since typing in full times can be finicky with the format etc.
A16:L16 is the starting row

Also not sure if it matters much. but does this clear the variables at the end?
Erase a ' Each element set to Empty/Free memory used by array
Erase b
Erase Bits

But well i guess if you're going to do that, this is what the whole thing looks like in this sheet.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
37	ID	Customer	Ticket #	Work Description	Date	Time Start	AM	Time End	AM	Range	Qty	Internal Notes
38	1			Text description etc..	29-Sep	11:00	AM	2:30	PM	11:00-2:30PM	3.5
39	2			Text description etc..	29-Sep	2:30	PM	5:00	PM	2:30-5:00PM	2.5
Sheet2

Cell Formulas
Range	Formula
J38:J39	J38	=IF(K38>12,TEXT(F38, "h:mm")&G38&"-"&TEXT(H38, "h:mm")&I38,TEXT(F38, "h:mm")&"-"&TEXT(H38, "h:mm")&I38)
K38:K39	K38	=IF(F38<>"",SUM(MOD(TIMEVALUE(TEXT(H38, "h:mm")&" "&I38)-TIMEVALUE(TEXT(F38, "h:mm")&" "&G38),1)*1440)/60,"")

Also in the range i only show the AM/PM if its over 12 hrs range

 

[Bond00]

It also had the same issue if it ended with 12pm too making the am time into PM

Book1
	D	E	F	G	H	I
22	10/9 11-1230pm Work on system repairs and firmware upgrades	9-Oct	11:00	PM	12:30	PM
Sheet1

 

[Peter_SSs]

I'm getting lost.

I thought the request was to break out elements from a longer text in col D. What does this have to do with that?

Like they could just type 1: Tab and it would enter in 1:00 and then type A or P tab and it would auto put in AM or PM et

Also not sure if it matters much. but does this clear the variables at the end?

No, but you can add that if you want. I have rarely found it to be an issue.

 

[Bond00]

I'm getting lost.

I thought the request was to break out elements from a longer text in col D. What does this have to do with that?

I was just answering you that's what was done before, that's why it was separated out (Time and the PM/AM in different cells) so it could be manually typed faster (in the past)

 

[Peter_SSs]

If somebody has already typed it into column D (with other text) and it has then been split up, why would that data be over-written? And would that be common?

Anyway, are any of the codes of use to you?

 

[Bond00]

If somebody has already typed it into column D (with other text) and it has then been split up, why would that data be over-written? And would that be common?

Anyway, are any of the codes of use to you?

no, now that you made this code it wont need to be entered manually ever again probably. but yeah that last bit of code is useful. but can you make that go into col K since theres other things pulling from that column already. I dont see in your code where its specifying the col for some reason unless its the (i, 1)-(i, 6) and its just using an offset instead of actual col #?

Oh also can i move the if check here so it will still run and try to find any with a # in front in case more is added later after it was ran once already?

 

[Peter_SSs]

but yeah that last bit of code is useful. but can you make that go into col K

Needs clarification. The code outputs 6 columns & that obviously cannot all fit into column K. Do you mean send the out to start at col K, thereby filling cols K:O or do you mean something else?

also can i move the if check here so it will still run and try to find any with a # in front

Definitely not. That would require a different code. How much different might depend on the answer to the first question above.
Would any additional data always just be added at the bottom or could you end up with lines that do not start with a digit and then lines that do and more lines that don't and then more lines that do etc?