Home » Questions » Computers [ Ask a new question ]

Convert Excel Text string to Time

Convert Excel Text string to Time

I'm trying to Export from an OLD System that stored Time in / Time out as a text string in 24 hour time.

Asked by: Guest | Views: 60
Total answers/comments: 2
Guest [Entry]

"This formula modified from Dave's and Tracy's will output the text string you want, and not require you to format the cell:

=TEXT(LEFT(A1,LEN(A1)-2) &"":""& RIGHT(A1,2), ""h:mm AM/PM"")

If you only want hours, then change the h:mm to h."
Guest [Entry]

"Not exactly sure how your values are stored in your cells, i.e. are they double quoted? prefixed with a single quote? It's going to matter because of how the leading 0 is handled.

You might try:

=TEXT(LEFT(A1,2) &"":""& RIGHT(A1,2), ""hh:mm AM/PM"")

My expectation is that this will work for most entries (morning entries might be a problem."