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.

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

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.

-------------

hope it helps

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

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

=TIMEVALUE(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2))

It handles military time in the format hhmm or hmm

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,

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,

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

*/

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.

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.

Adarsh:

Thanks for the information, it worked prefectly.

end of post