converting date and time as text into a usable date / time format in excel

Question

Want to convert into usable date/time format. Have seen =DATEVALUE(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),100,100))) used to produce date, but did not include time - I need time code to measure duration between 2 entries

Answer 1

Hi @Greg Moulton

If you get the time, please check whether my formulas are helpful:For hour number: =-LOOKUP(0,-RIGHT(LEFT(A2,FIND(":",A2)-1),2))

For minute number: =TRIM(MID(SUBSTITUTE(A2,":",REPT(" ",100)),100,100))

For second number: =-LOOKUP(0,-LEFT(TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",100)),100)),2))

AM or PM: =MID(TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",100)),100)),FIND(" ",TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",100)),100)))+1,2)

(If your time is like DST, MDT with 3 characters, then you may use =LEFT(RIGHT(A2,6),2) instead.)

Then you may combine them with "Time" function (=IF(B9="PM",TIME(B6+12,B7,B8),TIME(B6,B7,B8))), and set the cell format: h:mm:ss.

If you do not list the hour, minute and second numbers, you may try =IF(MID(TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",100)),100)),FIND(" ",TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",100)),100)))+1,2)="PM",TIME(-LOOKUP(0,-RIGHT(LEFT(A2,FIND(":",A2)-1),2))+12,TRIM(MID(SUBSTITUTE(A2,":",REPT(" ",100)),100,100)),-LOOKUP(0,-LEFT(TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",100)),100)),2))),TIME(-LOOKUP(0,-RIGHT(LEFT(A2,FIND(":",A2)-1),2)),TRIM(MID(SUBSTITUTE(A2,":",REPT(" ",100)),100,100)),-LOOKUP(0,-LEFT(TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",100)),100)),2)))).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

If you isolate the time information in the string, the TIMEVALUE function will convert the text to a numeric time.

=TIMEVALUE(MID(LEFT(A2,LEN(A2)-3),FIND("at ",A2)+3,100))

You mentioned that you want to compute time duration. If your time span starts before midnight and ends after, you will need to include the date so that subtracting 11 PM Monday from 1 AM Tuesday will properly result in 2 hours. The DATEVALUE function can be applied the same way.

=DATEVALUE(MID(LEFT(A2,FIND("at ",A2)-1),FIND(",",A2)+1,100))

Then you add the two to get a date-time value.