I’m working on a simple spreadsheet here at work, and the oddest thing happened. I accidently entered today’s date (07/26/2000) into a field that was formatted for monetary numerics ($XX,XXX.XX). When I entered it, Excel changed the field to $36,733.00.
Now, I understand it has to do something with the numbers I put in the field, but where the hell did it get $36,733.00??
1 is Jan 1, 1900. At 100 years it is 36525 (or so), and add a day for every day in 2000 after Jan 1, and you get your number. Wacky I know, but it is how MS decided to do it. Maybe it is based on some other system or protocol that lots of their customer’s use?
The only thing I can figure is there is a formula applied to that field. It’s trying to interpret your date into a number so it can complete the formula.
Wow, Engineer Don, that was quick and impressive. It looks like your answer is right on. Using the 36525 and adding 208 (today is the 208th day of the year) we get 36,733. That’s odd that they chose that method.
I also tried to enter a regular monetary number in the date-formatted field, but it just left the number alone.
Hmmm, why? can you think of a better one? All spreadsheets I have used use a count of days to represent dates because it is the easiest way to manipulate them (like find the number of days between two dates). can you think of a better system?
astronomers do the same thing and call it Julian date which is a continuous count of days starting from january 1 4713 BC. Because it results in a large number they often use the modified julian date (MJD) which is defined as
MJD = JD - 2400000.5
Excel could have used any count of days and they decided to use their own starting point. Nothing odd about it.
It just sucks when trying to create spreadsheets using historical data that goes back to the late 1800’s (Dow Jones start date). Totally misreads them and screws you up.