Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
. . 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? . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
"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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military Time in Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
How to record time in Excel in military (0730) format? | Excel Worksheet Functions | |||
getting military time format within excel | Excel Discussion (Misc queries) | |||
Is military time available in Excel? If so, how would I calculat. | Excel Worksheet Functions | |||
in excel totaling weekly hours military time | Excel Worksheet Functions |