0

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.

1
  • Well, what do we have for format HH:mm:ss.SSS ?
    – user145732
    Jul 13, 2012 at 5:16

6 Answers 6

3

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))

0
2

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))))

1
  • 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, 2012 at 3:10
1

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.

1

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.

1

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

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .