[John Smith[_7_]]

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[_7_]]

"John Smith" wrote in message
...
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[_3_]]

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.

"John Smith" wrote:


"John Smith" wrote in message
...
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


.

[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

--
Biff
Microsoft Excel MVP


"John Smith" wrote in message
...
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?

[David Biddulph[_2_]]

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


T. Valko wrote:
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


"John Smith" wrote in message
...
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?

[trip_to_tokyo[_3_]]

. . 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" wrote:

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[_7_]]

"trip_to_tokyo" wrote in message
...
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.

[[email protected]]

On Sunday, January 17, 2010 at 8:02:54 AM UTC+13, T. Valko wrote:
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

--
Biff
Microsoft Excel MVP


"John Smith" wrote in message
...
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?


This worked great for me. I have start and end times for a timesheet and the calculation still work as expected. Now I dont have to bother using the pesky : colon anymore even though it still works if used. My time cells are formatted [hh:mm] so thanks for this
Greg