Spreadsheet Page Blog
I've been working on the Excel 2010 Bible, and I read this passage in a section about data entry:
If a number contains a colon (:) or is followed by a space and the letter A or P, it may be converted to a time format.
Obviously, I knew this at one time, but I forgot it. If someone would have asked me yesterday, "Hey John, when I enter a time, can I just type a P, or do I need to type PM?" I would have replied that it's necessary to type PM.
An aging brain has room for only a limited about of Excel knowledge, so some of it goes away to make room for more. I probably used to know all of this, too:
- Type an integer from 0 through 9999, followed immediately by a colon, and it's interpreted as a time value. 0: is interpreted as 12:00:00 AM, and 9999: is interpreted as 2/19/1901 3:00:00 PM.
- Type an integer from 0 though 12, followed by a space and the letter P, and it's interpreted as a p.m. time. So 9 p is converted to 9:00 PM.
- Type an integer from 0 through 12, followed by a space and the letter A, and it's interpreted as an a.m. time. So 9 a is converted to 9:00 AM.
- Type an integer, followed by a colon and a value from 0 through 59, and it's interpreted as a time. A leading zero for the minute part is not required. For example, 9:2 is converted to 9:02 AM.
- Type an integer, followed by a colon and a value greater than 59, and it's interpreted as a time -- but Excel doesn't apply time formatting. For example, 6:200 is converted to 0.388888888888889. If you apply time formatting, it appears as 9:20 AM. (That's 6 hours plus 200 minutes.)
Feel free to list more Excel time-entry quirks -- even though I won't remember them.