|
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.
| ||||
|
feedback
|
|
Assuming the value is in cell A1, use this formula:
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:
| ||||
|
feedback
|
|
This formula modified from Dave's and Tracy's will output the text string you want, and not require you to format the cell:
If you only want hours, then change the h:mm to h. | |||
|
feedback
|
|
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: My expectation is that this will work for most entries (morning entries might be a problem. | |||
|
feedback
|
|
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)))) | |||
|
feedback
|
