Forum Discussion
Please help me find a solution you experts
To all of you Excel experts
Question: How can I extract the time to the D column? Also want to change the format to 24 hours
Cell E5: 8/26/23 12:16:32 am Asia/Seoul
In D5:
=--TRIM(MID(SUBSTITUTE(E5," ",REPT(" ",255)),255,300))
Format D5 as hh:mm:ss
Fill down.
- alternatively in 365:
=TIMEVALUE(TEXTBEFORE(TEXTAFTER(E1," ",1)," ",2))
or pre-365
=TIMEVALUE(MID(E3,SEARCH(" ",E3)+1,11))
and as suggested above use Number Format to format into military time but you can use TEXT() but then the output will be text and NOT a time VALUE:
=TEXT(TIMEVALUE(MID(E3,SEARCH(" ",E3)+1,11)),"[HH]:mm")
