Microsoft Excel - Converting military time to standard time

Asked By Denard Hall on 18-Nov-08 12:27 PM

I have read your posting about converting military time to standard time but got confused because nowhere was it explained subtract what from what. My excel spreadsheet contains both stand time 900 (for 9:00 a.m.) and 1400 (2:00 p.m.) throughout the spreadsheet. I would like a simple formula to convert the times to read 9:00 a.m. or 2:00 p.m. It there a simple way of doing this? Thanks in advance for any suggestions.

Solution

san san replied to Denard Hall on 18-Nov-08 03:00 PM

Hi

Want to convert military time to standard time ?
Try the bellow code..

'set military time 09:30:00
Dim sTime As String = "093000"

' convert to standard time
Dim dt As DateTime
dt = DateTime.ParseExact(sTime, "HHmmss", Nothing)
Dim sTheTimeFormatYouWant As String = String.Empty
sTheTimeFormatYouWant = dt.ToString("h:mm tt")

The ParseExact() function will then take your time string and change it into a DateTime type. (The day, month and year will be that day when it's run, as in DateTime.Now or DateTime.Today) You might want to enclose the ParseExact() in a Try-Catch clause in case your time numeral is incorrect and the parsing fails.

"HH" represents the zero-padded 24-hour hour
"mm" represents the zero-padded minute
"ss" represents the zero-padded second

"h" represents the 12-hour hour (no zero padding). Double "h" for zero-padding.
"tt" represents the time of day (AM or PM)

-------------
hope it helps

Use RoundDown function with Mod

[)ia6l0 iii replied to Denard Hall on 19-Nov-08 12:39 AM

Suppose, you have the military time in the cell A1, use this.

A2 =ROUNDDOWN(A1,-2)/2400+MOD(A1,100)/1440

A2 will contain the decimal value. Format A2 with any time format that you need. It will have the Standard Time

This will work.

Thanks

Srinath M S

Converting military time to standard time

mv ark replied to Denard Hall on 19-Nov-08 02:52 AM

This formula when placed in cell B1 will convert a military time in cell A1 to standard time
=TIMEVALUE(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2))

It handles military time in the format hhmm or hmm

try this

C_A P replied to Denard Hall on 19-Nov-08 04:14 AM

Selection.NumberFormat = "[$-409]h:mm AM/PM;@"

Supposing your 'hard' number is in cell A1, put this formula in B1:

=TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2))

You can then copy it down the column, etc. It converts the number 130338 into text as 13:03:38 then 'timevalue' converts the text into an Excel compatible serial number. You may see it displayed as .54419. If so just choose 'format' menu, Cells, number and '1:30:55 PM' as the format.

Cheers,

try this

C_A P replied to Denard Hall on 19-Nov-08 04:18 AM

Code Block

declare @theTime varchar(10) set @theTime = '13:50:00'

select right('0' + ltrim(right(convert(varchar,
cast(@theTime as dateTime), 100), 7)), 7)
as timeString

/* -------- Sample Output: --------
timeString
----------
01:50PM
*/

Thanks

Denard Hall replied to [)ia6l0 iii on 21-Nov-08 09:19 AM

Sinath:

Thanks for the information. Unforunatley, I did not want the time to rounddown but I was able to get close to the actual time. Thanks again.

Thanks

Asked By Denard Hall on 21-Nov-08 09:21 AM

Lalji:

Thanks for the information,it worked prefectly. I have not tried your longer SQL at this time. The short version work fine. Thnaks again.

Thanks

Denard Hall replied to C_A P on 21-Nov-08 09:23 AM

Adarsh:

Thanks for the information, it worked prefectly.

You are welcome

[)ia6l0 iii replied to Denard Hall on 24-Nov-08 12:54 AM

end of post