Convert Military Time to Standard Time

[LadyDi]

I have a report that pulls the time an order was placed. However, the time in the report is in military time. Before sending this report to my superiors, I want to convert it to standard (12 hour clock) time. Is there a formula that will do that? In other words, my report shows 14.22.00, I would like to convert that to 2:22.

 

[Steve R.]

Access help covers this under the "Format Function". Also there can be a complication if your time is actually GMT (or UTC) rather than (local) military time.

 

[LadyDi]

The report I'm running is not out of Access, it is out of our ordering system. I have no control over how the time appears in the report. I want to change it once I put it in Excel.

 

[NBVC]

If you do a Find/Replace on those cells (CTRL+H) and replace (dot) . with (colon) :

Then you should be able to format cells as TIME and select your desired format.

If you want a separate formula, then try:

=SUBSTITUTE(A1,".",":")+0

where A1 contains original text time.. and then format that result as TIME and select the desired format.

 

[Steve R.]

The report I'm running is not out of Access, it is out of our ordering system. I have no control over how the time appears in the report. I want to change it once I put it in Excel.

My Apologies, I did not notice that this was in the Excel sub-forum. I hope that others with better knowledge of Excel, such as NBVC's, will provide the answer.

 

[LadyDi]

That works perfectly. Thank you very much for your help.