Yet another extract datetime from text field query

[d101au]

Hello,

I have a column of cells with the following text format, "2:03pm September 27th – 3:12pm September 27th".

I want to extract the two date-time combinations into separate columns in Excel date-time format to enable further processing (e.g. to calculate duration of the event and to sort event rows into date time order)

So far I have found methods online to break the text into two elements (assuming the above text is in cell A1):

=LEFT(A1,FIND(" – ",A1)-1)

extracts the first date-time combination

and

=RIGHT(A1,LEN(A1)-(FIND(" – ",A1)+4))

extracts the second date-time combination.

I can extract the day of the month as a number from the extracted date-time result using (assuming the result of the first date-time extraction was placed in cell A2):

=VALUE(MID(A2,LEN(A2)-3,2))

The month can be extracted as a number using:

=MONTH(DATEVALUE(MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2))&" 1"))

And the time can be extracted in Excel format via:

=TIMEVALUE(SUBSTITUTE(LEFT(A2,FIND(" ",A2)-1),"pm"," pm"))

Let us assume the YEAR is the current Excel year.

Combining the day number + month number + current year + the time into a single cell in Excel datevalue format can be done (assuming the day number value is in cell A4, the month number in A5 and the time in A6 respectively) via:

=DATEVALUE("01"&"/"&TEXT(DATE(2000,A5,1),"mm"))+(A4-1)+A6

Is there a better/more elegant method of extraction to achieve the same result?

Ciao

 

[pbornemeier]

Welcome to the Board!

If you use VBA the SPLIT function could split the cell into chunks divided by each space

Option Explicit

Sub ExtractDates()

    Dim x
    x = Split(Range("A1").Value, " ")
    Range("B1").Value = DateValue(x(1) & " " & Val(x(2))) + CDate(x(0))
    Range("C1").Value = DateValue(x(5) & " " & Val(x(6))) + CDate(x(4))
    
End Sub