Automatically convert military time to display as standard time format

[worl4125]

I'm not sure if this is possible, but is there a formula I can enter a time in military time (ex: 0230) and have it automatically display as standard time (ex: 2:30 AM)? Basically I'm looking for a quick way to enter times into my spreadsheets without having to type the format every time.

Thanks.

 

[cchavez24]

just type the times in, then highlight the column and format it.

let me know if you have any questions.

 

[worl4125]

No. Not what I mean.

Say you want to enter the time twelve-thirty PM. You have to enter "12:30 PM". This is true whether or not you format the cells to a "Time" format or not.

What I'm trying to figure out is a custom formula or custom format (or a combo of both) that will allow me to simply enter "1230" and the cell will display "12:30 PM".
Another example might be entering "0230" and the cell displays "2:30 AM".

In other words, I want the format to recognize the first two integers in the number string as being from the 24-hour (military time) format and change it over to the 12-hour (standard time) format, adding "AM" or "PM" as required.

The closest method I've come up with involves using the TIMEVALUE function.
Example:
In A1 I enter 1230.
In B1 I enter =TIMEVALUE(LEFT(A8,2)&":"&RIGHT(A8,2))
Then I format B1 using the Time format

So if I enter 1230 in A1, then B1 shows 12:30 PM; 1645 displays as 4:45 PM; 2210 displays as 10:10 PM; and so forth.

There two problems with this method:
1) its very cumbersome to use two cells like this
2) the conversion doesn't work for early AM hours (ex...0820, or 0210, or 0545)...basically any military time that leads with a "0".

Anyone have any other ideas on how this might be accomplished?
Thanks.

 

[cchavez24]

Enter the way you want in a column, then use two helper colmns to split the hours and minutes. Use the formula above timevalue.
Therein embed an if formula if the hour value is less than 10 then concatinate the zero in.

If that fails, I believe that the way excel saves dates is whole number of days since 1/1/1900 and time divided by 24 hours. So you could use the helper cell system and multiply the hours by 60 plus the minute value then divide by 24 times 60. That should get you the time value in decimal and that you can format.

Let me know if this works for you.

 

[FDibbins]

enter "1230" and the cell will display "12:30 PM".

without using VBA, that is not possible, but perhaps this, in a helper cell?
=--(LEFT(F6,2)&":"&RIGHT(F6,2))

2) the conversion doesn't work for early AM hours (ex...0820, or 0210, or 0545)...basically any military time that leads with a "0".

if you have time with a leading 0 - and that's what the formula bar shows, then that is more than likely not real time, but text that looks like time

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Thu 02 Mar 2017) is actually 42796

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

 

[worl4125]

without using VBA, that is not possible, but perhaps this, in a helper cell?
=--(LEFT(F6,2)&":"&RIGHT(F6,2))


if you have time with a leading 0 - and that's what the formula bar shows, then that is more than likely not real time, but text that looks like time

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Thu 02 Mar 2017) is actually 42796

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

I see what you're getting at. So given the way excel handles time, the math involved with hours less than 10 simply won't work out. I've never used VBA before....hell, never studied VB before...have always been a C# man myself.. Maybe I'll look into it one day. This was more of matter of personal curiosity than anything important anyways.

Thanks for the help. You've given me things to think about.

 

[Peter_SSs]

1. Do you need the times to be actual times?
That is, will you need to do some subsequent calculation(s) with those times? For example, work out how many minutes between two of them.

2. Otherwise, would it be acceptable if the cells are text and just look like times (2:30 PM)?

3. Are we talking about entry in a particular column only? If so, what is the cell formatting currently in that column? (eg General, Time, Number etc)

 

[RJOIII]

I'm not sure if this is possible, but is there a formula I can enter a time in military time (ex: 0230) and have it automatically display as standard time (ex: 2:30 AM)? Basically I'm looking for a quick way to enter times into my spreadsheets without having to type the format every time.

Thanks.

You can use =TIME(ROUNDDOWN(S4/100,0),S4-(ROUNDDOWN(S4/100,0)*100),0) where S4 is your time in 1630 for 4:30 PM format.

 

[Phil Smith]

Page 122 of Excel 2016 VBA and Macros by Bill Jelen and Tracy Syrstad gives a perfect example of a solution to this problem. I haven't quoted the code here as I am not sure of the copyright issues.