CODEWITHSUNDEEP
excelexcel-formula
user2242044
user2242044

Reputation: 9213

Excel Convert text to timestamp

I have the following text timestamp in cell A1. 22-OCT-13 03.40.00.000000000 AM. In B1 I have the following formula: =TIME(HOUR(MID(A1,11,2)),MIN(MID(A1,14,2)),SECOND(MID(A1,17,2))).

It yields 12:40 AM. The hour function returns zero, but the minute function works fine. I can't figure out what's up with it.

Upvotes: 1

Views: 10036

Answers (1)

Jerry
Jerry

Reputation: 71538

MIN is the 'minimum' formula that returns the smallest value out of a list of values, not the MINUTE formula.

I think you are not familiar with TIME(), HOUR() or MINUTE().

TIME() takes 'normal' values and converts them into a time value. If you have TIME(H, M, S), H is the number of hours (not a timestamp), M is the number of minutes (not a timestamp either) and S is the number of seconds (yet again, not a timestamp).

HOUR() takes a time value and extracts the hour from it.

MINUTE() and SECOND() are similar to HOUR(). What you probably meant to have is:

=TIME(MID(A1,11,2),MID(A1,14,2),MID(A1,17,2))

When you use MID() you are getting a number, not a timestamp. So, HOUR takes that number, interprets it as a timestamp and return the hour. In your example, the first MID returns 03. In excel, if you convert that to a timestamp, you get 03/01/1900 00:00 (in dd/mm/yyyy hh:mm format) and the hour is thus 0.

EDIT:

By the way, you could use this formula if you want to keep the date and time (not forgetting the AM/PM at the end:

=SUBSTITUTE(SUBSTITUTE(A1,".",":"),":",".",3)*1

The original date/time is almost one that can be understood by Excel. Replace all . with : and then replace the 3rd (last) : by . and multiply by 1 to tell Excel to convert that to a number. Just format the result as time and that should do it.

Upvotes: 3

Related Questions