7-11 tunrs to 11-Jul turns to 39274... Why?

[Guest]

This is just a curiosity question, really.

I make a budget spreadsheet, and dear old 7-11 is on there every once in a
while. If I forget to make the cell a text/general cell first, it
automatically changes the entry 7-11 to 11-Jul. I understand why it does that
well enough. My question is, why, when I select the same cell again and
switch it to text/general, does it display 39274? Is that some sort of magic
number?

For the record, I've tried the same with other "dates," and different
numbers come up. Maybe it's a random number that is related to the date? Does
anyone know for sure?

 

[Guest]

Dates are held as a number relative to 1 Jan 1900 (Day number =1) so 11th
July 2007 is day number 39274.

HTH

 

[David Biddulph]

Excel dates count from 1 Jan 1900, hence your 39274 (or they count from 1904
if the 1904 date system is selected, which it is by default on a Mac).

 

[Pete_UK]

Dates in Excel are stored internally as the number of elapsed days
since a reference date of 1st January 1900. If your date is in this
year, then this will generally be about 39000 days since that
reference date. By formatting the cell as a date (11-Jul, or July 11,
or whatever) you are only asking Excel to change how the underlying
value is interpreted and displayed - the value is still round about
39000 as you have found.

Hope this helps.

Pete

 

[Guest]

It turns out that dates are really numbers with a "date" format:

1/18/1945 is actually 16,455
1/1/2001 is actually 36,892
11-Jul is actually 39,274

 

[Guest]

Thanks to everyone who replied. My curiosity has been satisfied. I kind-of
assumed it had to be something like that. I wonder what happens if you put a
date before 1900.. does it self destruct? That might be fun to watch...

 

[Pete_UK]

Try it - you can enter it as text, eg '03/03/1815, but if it is a
formula which results in a date before 1/1/1900 you will get a cell
full of # symbols.

Pete

 

[Guest]

Interesting.

*stifles laughter* Oh, Microsoft... what will you do next.