Military time in Excel

[tazman99]

Hello, I know this should be very simple, but I cannot find the answer.

How can I make it so that when I input the military time into an Excel 2003 worksheet, the colon is automatically typed for me.

For example, I just want to type 2300 and then have 23:00 typed for me. I tried using the hh:mm cell format, but when I apply that format before I do the data entry, both 00:00 shows in the cell ref and then date and time show in the formula.

Thanks much!

 

[carrr]

Format Cells > Custom > type in 00\:00

Tired of waiting for an answer? Try asking better questions. See: faq222-2244

 

[tazman99]

Thank You Carr, this worked perfectly.

What exactly does the \ mean? Does the \ represent military time?

~Tazman

 

[AnotherHiggins]

tazman99,

Just a warning about carrr's solution. That will make the cell look like time, but the cell will in fact just be a number.

Why does that matter? Let's look at an example:
[li]Format column A as 00\:00[/li]
[li]In A1, enter 2300[/li]
[li]In A2, enter 1545[/li]
[li]In A3, enter =A1-A2[/li]

Now, without any special formatting:
[li]In B1, enter 23[highlight]:[/highlight]00[/li]
[li]In B2, enter 15[highlight]:[/highlight]45[/li]
[li]In B3, enter =B1-B2[/li]

A3 now reads 7:55 <-this is WRONG
B3 now reads 7:15 <-correct answer!

It's best to stick with Excel's native time functionality. So you're going to need to enter the colon every time.

See thread68-1025780 for a quick overview of how Excel handles dates and times.

It might seem like a hassle now, but if there is any chance that you will ever want to do anything else with the data, you're better off doing it right from the beginning.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[petrosky]

Hi carr,

It can be done by having a formula in a hidden column to build the times for you.

EG. You enter times in column A.
In your hidden column B, you would place this formula.

=IF(A1>0,IF(LEN(TRIM(A1))=3,"0"&LEFT(A1,1)&":"&MID(A1,2,2),LEFT(A1,2)&":"&MID(A1,3,2)),"0")

I hope this helps.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[petrosky]

Edit,

Sorry, anotherhiggins is quite correct. I was using this as part of a larger timesheet calculator.

=ROUNDUP((SUM((B2-B1)/0.04166667)*60),0)

This would calculate the minutes between B2 and A2

I apologise for any mix up.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[tazman99]

Hi anotherhiggins, thank you for the 'heads up' on the formatting for military time.

~tazman

 

[SkipVought]

Why are Dates and Times so much trouble? faq68-5827

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[AnotherHiggins]

tazman99,

Happy to help. I remember being new to Excel and thinking that the way it handled dates/times must involve the PFM Principle. That's where logic takes you so far, then you apply Pure [Freaking] Magic and end up with the result. But once you get your head around it, the logic behind Office's times is quite simple.

SkipVought,

Great FAQ!!

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.