help with converting time from a text value

MonteJ

New member
Joined
Apr 16, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
365
I need to convert the time in each column from text. Any help would be appreciated. The timevalue formula hasn't worked for me.View attachment Chats.xlsx
 
Time only? or date & time?

In attached cell F2 has formula for time only from your text:
Code:
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(TRIM(B2)," ","¬",4),FIND("¬",SUBSTITUTE(TRIM(B2)," ","¬",4))+1,99),"am", " am"),"pm"," pm"))

Cell K2 has the date only from your text:
Code:
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(TRIM(B2)," ","¬",4),FIND(" ",TRIM(B2))+1,FIND("¬",SUBSTITUTE(TRIM(B2)," ","¬",4))-FIND(" ",TRIM(B2))-1),"st",""),"nd",""),"rd",""),"th",""))

Cell P2 is just the last two added together.
All values are now proper Excel times and dates (numbers).
 

Attachments

  • ExceklGuru11128Chats.xlsx
    21.7 KB · Views: 10
Last edited:
Too late, you got both, but cell F2 gives just the time - see attachment in msg#2
 
Would a Power Query solution be of any use?
 
Back
Top