Data Entry Quirks

Category: General Weirdness | [Item URL]

When you enter a numeric value into a cell, Excel normally ignores any leading space characters. For example if you type [space]123, Excel discards the leading space and the value 123 appears in the cell.

When you enter a date or time value, however, leading spaces are not ignored. Rather, the date or time is entered as a text string and the leading spaces remain. For example, if you type [space]12/5/2009, this entry is not entered as a date. Similarly, [space]5:00 is not entered as a time value. These text strings appear exactly as you typed them, including the leading space -- most of the time.

Here comes the odd part.

Type [space]:0, and what do you get? You get a value: -0.000694444444444444. Excel formats the cell as a time value, so all you see is a series of hash marks: ######.

But if you type [space]:1, you get the time value, 0:59. Type [space]:2, and you get the time value, 1:59. [space]:3 gives you, 2:59. 

Now lets add an am/pm indicator. Type [space]:0 am and you get 2982616.16248843.

On a hunch, I Googled for the integer portion of that number and found this:

Installing Norton Antivirus 2008, I encountered this wonderful dialog box. I only needed to wait 4,294,967,294 minutes for current file updates to download! That's 71,582,788 hours, or 2,982,616 days. That's 8,171 years.

So, we have a similar time-related bug in both Excel and Norton Antivirus.

2,982,616 days is pretty close to 4,294,967,295 minutes -- which is (2^32)-1. Someone who understands how time values are stored internally might be able to explain what's going on.

Me? I'll just call it a harmless Excel oddity. At least I think it's harmless. I can't imagine any situation in which this could actually cause a problem.

(Thanks to Tadeusz Jankowski for bringing this to my attention)