Convert time to military

[datadummy]

I am trying to convert time to military but can't seem to get it to work. I have gone into format and changed the cell(s) to h:mm or h:mm AM/PM or even the various options within the time format "13:30", "13:30:55". Regardless of what format I choose the cell always turns to 0:00 and then in the formula bar it always shows the data as "2/23/1904 12:00:00 AM". Any thoughts on how to alleviate this and have it show as military time?

 

[Rick Rothstein]

I am trying to convert time to military but can't seem to get it to work. I have gone into format and changed the cell(s) to h:mm or h:mm AM/PM or even the various options within the time format "13:30", "13:30:55". Regardless of what format I choose the cell always turns to 0:00 and then in the formula bar it always shows the data as "2/23/1904 12:00:00 AM". Any thoughts on how to alleviate this and have it show as military time?

Use a Custom Format using this type pattern... hhmm

 

[datadummy]

Use a Custom Format using this type pattern... hhmm

I gave that a shot and it left the times as "0000".

 

[Rick Rothstein]

I gave that a shot and it left the times as "0000".

That is what you would get if your cell does not contain a time value in it. Are you sure you applied the Custom Format to a cell or cells having real dates and times and/or just real times in it?

 

[Brian from Maui]

I gave that a shot and it left the times as "0000".

Are you converting via a formula or with the cell formatting?

Can you give an example?

 

[datadummy]

That is what you would get if your cell does not contain a time value in it. Are you sure you applied the Custom Format to a cell or cells having real dates and times and/or just real times in it?

So far I have tried entering just times for example 1130 and it changes the time to 00:00.

 

[datadummy]

Are you converting via a formula or with the cell formatting?

Can you give an example?

So far just with the formatting, so as of now I have numbers in "0000" form i.e. 1130,0457,1840. I tried a macro and it seemed to be working and then stopped for some reason.

 

[Brian from Maui]

So far just with the formatting, so as of now I have numbers in "0000" form i.e. 1130,0457,1840. I tried a macro and it seemed to be working and then stopped for some reason.

As Rick mentioned, 1130 is not a true time value. You can either Custom Format the cell that houses 1130 as "00\:00" or you can convert 1130 in another cell with

=TEXT(A1,"00\:00")+0

If you Custom Format the cell, you'll need to convert that cell using the TEXT formula if you wish to do calculations.

 

[datadummy]

As Rick mentioned, 1130 is not a true time value. You can either Custom Format the cell that houses 1130 as "00\:00" or you can convert 1130 in another cell with

=TEXT(A1,"00\:00")+0

If you Custom Format the cell, you'll need to convert that cell using the TEXT formula if you wish to do calculations.

I used the custom format "00\:00" and that did the trick. Thanks for the help!