calculation unexpected

[zxcv]

Hi,
I'm getting a calculation result I didn't expect and don't know where it's
coming from. I pasted a formula into a new cell and accidentally didn't
include the dollar sign to make it an absolute reference. Still, it did a
calculation on a dollar amount of $600, and a date of Mar 31 1993, and came
out with the result $20,435,400! I can't figure out how Excel is getting
this! I did 20,453,400/600, which gave me 34,059. But how does Excel get
that number from Mar 31 1993?

 

[Mike H]

Select the cell that has the date in and change the format to number and all
will be revealed.

Dates in Excel are stored as numbers and formatted to appear as dates to you
and I.

Mike

 

[Mike H]

Looking slightly deeper

31 Mar 1993 = 34059 *$600 = $20,435,400

Mike

 

[zxcv]

OK, but what is the relationship between the date Mar 31 1993 and the number
34,059?

 

[Mike H]

An excellent explanation here

http://www.cpearson.com/excel/datetime.htm

 

[Ron Rosenfeld]

Hi,
I'm getting a calculation result I didn't expect and don't know where it's
coming from. I pasted a formula into a new cell and accidentally didn't
include the dollar sign to make it an absolute reference. Still, it did a
calculation on a dollar amount of $600, and a date of Mar 31 1993, and came
out with the result $20,435,400! I can't figure out how Excel is getting
this! I did 20,453,400/600, which gave me 34,059. But how does Excel get
that number from Mar 31 1993?

Excel counts 1/1/1900 as day 1.

Mar 31 1993 is day 34059.

That is how Excel stores dates.
--ron

 

[Sandy Mann]

XL counts the days since 1900 - with an error because it wrongly assumes
that 1900 was a leap year . Day 34,059 is therefore31 March 1993

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

Essentially, those numbers are just a counter from a base date.
(Tools|Options|calculation tab|1904 date system is where you'd toggle the
setting.) Most windows users don't use the 1904 date system. Most (many or
some???) Mac users use 1904 as the base date.

So there have been about 34,059 days since Dec 31, 1899.

(Just to make life interesting, there is a problem. Excel (and other
spreadsheet programs) treat February 29, 1900 as a leap year. It wasn't. This
isn't important to me (or most/many/some???) users <vbg>.)