In his last desperate act before taking a handful of Ambien, M. DeRevere asks:
How can I program a cell to accept time formulas without putting the (:) colons between the numbers… if you could format the cell to accept just number such as 2300 and it formats it as 23:00 it would save me sooooo much time?… HELP
Well hold off on the pills M. Because I'm rockin the Excel skillz , and I happen to know the answer.
M's question revolves around "military" time (the 24 hour clock). For you civilians out there, 23:00 hours is 11:00 pm in military time. The trick is to apply some custom number formatting.
1. Highlight the column into which you'll be entering your data.
2. Bring up the Format Cells dialog box and select the Number tab.
3. Choose Custom from the Category list.
4. In the Type field, enter 00\:00
At this point, any number you enter will automatically have a colon inserted into the number (which I suppose is better than having a number inserted into the colon ).
There are other ways to convert numbers to military time using formulas and such. This is probably the easiest way.
I would note that this is not truly a time format so there is nothing stopping anyone from putting something silly in a cell like 3345. But for relieving some of the burden of data entry, this techinque serves the purpose.
Keep in mind that performing mathematical operations on these pseudo-time values is not at all reliable.
11:30 – 11:15 gives you 00:15, which is correct.
13:10 – 11:15 gives you 1:95, which doesn't make sense as a time.
It's likely that many a military battle has been lost because of these incorrect time calculations. At the very least, some Excel-using soldiers have showed up late for mess.
You can always convert these pseudo times to real times using a formula like this:
=TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2))
Then format the formula cell as a time.
John: Please don't say mess after my colon joke.
In any case, your warning rings true. If these 'times' are to be used in calculations, then a helper column with your suggested TIMEVALUE formula should be used.
Thanks J-walk.
Add data validation to the cell(s) to restrict entry to whole numbers between 0000 and 2359.
CarlS: Great idea.
I simply use "hhmm" in the custom syntax format and I have the same result without the problem for calculation (i.e. 13:10 – 11:15 gives you 1:55).
Sebastien: Using hhmm gives displays 0000 for anything I enter.
CarlS: The data validation is a great idea, but it will accept mistakes like "1089", since it wouldn't know it is a time format..
Datapig, that's odd, maybe it's due to different international settings in Windows. I'm set as English – Canada.
You can try adding a location settings to the custom format like this for "English – Canada"
[$-1009]hhmm
I also sent you my workbook by e-mail.
If you want to learn more on international number formats, you can go the the followin site
http://office.microsoft.com/en-ca/excel/HA010346351033.aspx
That is odd. Thanks for the link. I'll take a look.
I think I might know why. Do you have integer in the cell or fractions?