Having trouble with date & time formula due to formatting

[tripkat]

I need to be able to calculate the number of hours between two entries, but the PM/AM doesn't have a space before it so the formulas for date/time difference aren't calculating. Here is what the entries look like and ultimately I need the duration between the two even when it crosses midnight:

Actual Start Time Actual End Time
3/26/2018 3:54PM 3/26/2018 9:48PM

I've tried the left/right formula to add the space between the minutes and PM/AM but since the date is m/d/yyyy it's not always the same number of spaces to the left.

[XLent]

a few options, one:

Please Login or Register  to view this content.

where A3 holds start, and B3 end.

[tripkat]

Attachment 646777

I think this is what will work because the formatting is the issue

=CONCATENATE(TEXT(LEFT(A2,FIND(" ",A2,1)-1),"mm/dd/yyyy")," ",TEXT(TRIM(MID(LEFT(A2,LEN(A2)-2),SEARCH(" ",LEFT(A2,LEN(A2)-2)),LEN(LEFT(A2,LEN(A2)-2)))),"hh:mm")," ",RIGHT(A2,2))

[tripkat]

Thank you so much!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.