Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
Dates are held as a number relative to 1 Jan 1900 (Day number =1) so 11th
July 2007 is day number 39274. HTH "Lauren Giles" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
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). -- David Biddulph "Lauren Giles" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
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 On Feb 23, 7:36 pm, Lauren Giles wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
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 -- Gary's Student gsnu200707 "Lauren Giles" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
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... "Lauren Giles" wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
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 On Feb 23, 7:59 pm, Lauren Giles wrote: 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... "Lauren Giles" wrote: 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?- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
7-11 tunrs to 11-Jul turns to 39274... Why?
Interesting.
*stifles laughter* Oh, Microsoft... what will you do next. "Pete_UK" wrote: 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 On Feb 23, 7:59 pm, Lauren Giles wrote: 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... "Lauren Giles" wrote: 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?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why number 8 turns into a 0? | Excel Discussion (Misc queries) | |||
Automatic Calculation Turns Off Intermittently | Excel Worksheet Functions | |||
Bug? Autocalculation turns itself on... | Excel Discussion (Misc queries) | |||
If value in column is yes; entire line turns red | Excel Discussion (Misc queries) | |||
blank cell turns to 0 | New Users to Excel |