Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

For example 3pm, is entered as 1500 (no colon)

Is there a simple way I can convert these Values to a 12 Hour time with AM / PM?

I've got over 3000 entries - and I'm almost to the point of manually editing, so please any suggestions are appreciated.

share|improve this question
    
Well, what do we have for format HH:mm:ss.SSS ? –  user145732 Jul 13 '12 at 5:16
add comment

6 Answers

Assuming the value is in cell A1, use this formula:

=TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

What's happening is it's taking a value assume to be 1 or 2 digits for the hour and always 2 digits for the minute. For example, 1500 = 3:00 PM, 900 = 9:00 AM. You may need to format the cell as a time instead of a number.

If you have values that are earlier than 1:00 AM and your data does not have leading zeros, you'll want this more complex formula instead:

=TIMEVALUE(IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2),"0")&":"&RIGHT(A1,2))

share|improve this answer
    
+1 Converted to time, I left it as a string. –  DaveParillo Oct 6 '09 at 20:57
add comment

In Excel 2003 these formulas worked okay until it hit a time with no hour digit (the first hour after midnight) then it produced a #VALUE! error. I added an IF statement to deal with that first hour where there were only single or double numbered minutes :-

=TIMEVALUE(IF(LEN(A1)<=2,"0:"&RIGHT(A1,2),(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))))

share|improve this answer
    
Since I am reviewing late answers, I would suggest for this one to use the "code block" format with 4 spaces instead of the quote-style. But otherwise thanks for the answer. ^1 –  eckes Dec 13 '12 at 3:10
add comment

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.

share|improve this answer
add comment

If you have the Time in such numeric way (900 -> 9am) or (0900 -> 9am) [without the colon]

There is a much more simple way to convert it to a Time format, just separate hour form minutes:

  • Use function INT(YourCell/100) as the Hour part
  • Use function MOD(YourCell;100) as the Minute part
  • Use value 0 as the Second part

Then use the function TIME to convert it to time type, so all combined would look like:

  • =TIME(INT(YourCell/100);MOD(YourCell;100);0)

Now you only need to give it the Time format you want (1:30 PM on the list for Time).

The trick is that if you divide by one hundred and take the integer part you will loose the minute part, so you will have the "Hours", by other part if you take the module of dividing by one hundred you will get the minute part losing the hour part... then you only need to combine them.

share|improve this answer
add comment

The answer is much simpler. I had the same problem.

Use custom format 00\:00 on the input cell.

To turn that cell into an actual time to use in calculations use
=TIME(TRUNC(A1/100),MOD(A1,100),0)

Works perfectly for me ;)

source: http://www.pcmag.com/article2/0,2817,2316755,00.asp

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.