Time Formatting

[rpcoates1956]

I have a worksheet that I record time in military time. I want to be able to enter 9 and have it formatted to 9:00 or enter 20 and formatted to 20:00. I have looked thru the custom formatting and cannot find the formatting that will accomplish this task. Can anyone help me. Thanks.

[Special-K]

How do you intend to enter 9:30 ? Or 9:45 ?
Or will you only enter hourly times?

[rpcoates1956]

The time most entered will be on the hour (9:00) or the half hour (9:30). I do not need the date in the formatting as this is in a separate column.

[Special-K]

Question remains unasnwered.
Last time of asking then I'll move onto another problem.

How do you intend to enter the time of 9:30 ?

[rpcoates1956]

I plan to enter 9:00 as 9 and 9:30 as 930.

[Special-K]

I think you may have to do it with a formula on each row rather than formatting a cell.
This is because if you enter 930 the length is three characters but entering 9 the length is only one character and the alignment will be out.
It's a bit like saying I want to enter a series of three digit numbers, but if the number is a multiple of 100 I am only going to enter one digit and I want Excel to move that digit to the hundreds column.

[rpcoates1956]

Could I use formatting if I entered 9:00 as 0900? That way I will always being entering 4 digits. This way when I get to 10:00 I would enter it in as 1000.

[Special-K]

That will probabloy work as long as you don't do any time calculations, since 0900 though it displays as 09:00 will be regarded by Excel as nine hundred.

Use Custom format of

00\:00

[rpcoates1956]

I do not have the custom formatting 00\:00 in Excel 2013. Does this mean I am out of luck?

[Special-K]

You have to define it.
I just tried it on Excel 2007 and it works fine.

Right click the cell, select Format cells, pick Custom from the list.
Below where it says Type enter 00\:00

[rpcoates1956]

Got it. Thanks for your help.

[63falcondude]

Just note that this will not be seen as an actual time to Excel.

[Special-K]

...hence my refernce in post #8 to not doing time calculations as it will be regarded as nine hundred for 9:00