Military Time in Excel

[John Smith]

I would like to create a timetable in Excel with the format in the 24 hour
clock without the colon, which I believe is known as military time. I am
leaving the colon out for a number of reasons, aesthetic and to speed up
entry.

I wish to enter the time, for example, 8:30 am as 0800 and have Excel
interpret it as a time, as these will be graphed, so need to be interpreted
as time, and not plainly a number. At the moment Excel simply shows "800"
when I type 0800.

I have tried formatting all the cells as Custom "hhmm" instead of "hh:mm",
but when I enter the time data, it simply shows as 0000 and clicking on the
cell shows the formula of "dd/mm/yyyy hhmm".

Any ideas?

 

[John Smith]

I would like to create a timetable in Excel with the format in the 24 hour
clock without the colon, which I believe is known as military time. I am
leaving the colon out for a number of reasons, aesthetic and to speed up
entry.

I wish to enter the time, for example, 8:00 am as 0800 and have Excel
interpret it as a time, as these will be graphed, so need to be
interpreted as time, and not plainly a number. At the moment Excel simply
shows "800" when I type 0800.

I have tried formatting all the cells as Custom "hhmm" instead of "hh:mm",
but when I enter the time data, it simply shows as 0000 and clicking on
the cell shows the formula of "dd/mm/yyyy hhmm".

Any ideas?

Corrected 8:30 to 08:00

 

[trip_to_tokyo]

You could format as Number.

This would give you, "military time" for the hours between 1000 and 2400.

For the time prior to 1000 you would not get the leading zero.

Not sure if this would be acceptable to you though.

Please hit Yes if my comments have helped.

Thanks.

 

[Ron Rosenfeld]

I would like to create a timetable in Excel with the format in the 24 hour
clock without the colon, which I believe is known as military time. I am
leaving the colon out for a number of reasons, aesthetic and to speed up
entry.

I wish to enter the time, for example, 8:30 am as 0800 and have Excel
interpret it as a time, as these will be graphed, so need to be interpreted
as time, and not plainly a number. At the moment Excel simply shows "800"
when I type 0800.

I have tried formatting all the cells as Custom "hhmm" instead of "hh:mm",
but when I enter the time data, it simply shows as 0000 and clicking on the
cell shows the formula of "dd/mm/yyyy hhmm".

Any ideas?

Formatting a cell for time only changes how the cell contents is *displayed*.
It does NOT change how the input is parsed.

To do what you want requires a VBA event-triggered macro, (Or a formula in an
adjacent column).

Here's one way with a macro:

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

Be sure to set rg to the range where you want this to occur.

==========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range, c As Range

'in what range might you be entering Times?
Set rg = Range("A:A")

If Not Intersect(Target, rg) Is Nothing Then
Application.EnableEvents = False
For Each c In Intersect(Target, rg)
If c.Value >= 1 Then
c.NumberFormat = "hh:mm"
c.Value = TimeValue(Format(c.Value, "00\:00"))
End If
Next c
End If
Application.EnableEvents = True
End Sub
=============================================
--ron

 

[T. Valko]

While I agree that entering a lot of time values can be a real pita,
entering the values as you want to do will usually make things much more
complicated than you think. For example, if you need to base calculations on
these entries. You had something that was relatively easy to calculate and
turned it into something much more complicated.

So, do the benefits of entering 100 as opposed to entering 1:00 AM outweigh
the complications?

IMHO, they do not!

What you could do is enter the time as 100 then have Excel automatically
convert that entry to a true Excel time value.

See this:

http://www.cpearson.com/Excel/DateTimeEntry.htm

 

[David Biddulph]

You don't need to enter 1:00 AM; 1:00 would do.

 

[trip_to_tokyo]

. . or you could try this:-

Check out this link...

http://www.cpearson.com/excel/DateTimeEntry.htm
--
- with acknowledgement to Jim Thomlinson.

Please hit yes if my comments have helped.

Thanks.

 

[John Smith]

You could format as Number.

This would give you, "military time" for the hours between 1000 and 2400.

For the time prior to 1000 you would not get the leading zero.

Not sure if this would be acceptable to you though.

Please hit Yes if my comments have helped.

Not really, as the times need to be graphed, and formatted as number, the
number after 59 is 60, but in minutes it is 00, so the graphs would be
wrong - Thanks for your comment though.