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?

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

"zxcv" wrote:

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?

Looking slightly deeper

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

Mike

"zxcv" wrote:

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?

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

"Mike H" wrote:

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

"zxcv" wrote:

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?

An excellent explanation here

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

"zxcv" wrote:

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

"Mike H" wrote:

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

"zxcv" wrote:

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?

On Fri, 13 Jul 2007 06:26:04 -0700, zxcv
wrote:

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

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


Replace @mailinator.com with @tiscali.co.uk


"zxcv" wrote in message
...
OK, but what is the relationship between the date Mar 31 1993 and the
number
34,059?

"Mike H" wrote:

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

"zxcv" wrote:

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?

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.)





zxcv wrote:

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

"Mike H" wrote:

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

"zxcv" wrote:

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?


--

Dave Peterson