Help with determining # of days from certain dates

[JBrewster]

I need to determine the # of days from several dates for about 2 year
past until, say May 1, 2013. In Column 1 I have 5/1/2013 for about 3
rows, and in Column 2 have dates beginning with 6/1/2010 (B2) and movin
down about a month at a time so the last row is B31, with 7/12/2012
Column 3 rows use the formula =(A1-B1), changing the row listed in th
formula for each row down. The first answer for C1 is 1065, and th
numbers change correctly as we go down the rows. However, A31 read
5/1/2013, B31 reads 7/12/2012, but C31 gives 10/19/1900, not 284 as i
should. In "Show Formula", I can see the numbers and subtract them t
get 284, but Excel will not allow me to put the numbers in. What is th
problem here? There are most likely other ways to get this done, bu
this is how I tried. OK through 30 rows, but 31 is out of whack. An
suggestions?
Thanks for your help

 

[joeu2004]

In Column 1 I have 5/1/2013 for about 31 rows,
and in Column 2 have dates beginning with 6/1/2010 (B2)
and moving down about a month at a time so the last row
is B31, with 7/12/2012. Column 3 rows use the formula
=(A1-B1), changing the row listed in the formula for each
row down. [....]
A31 reads 5/1/2013, B31 reads 7/12/2012, but C31 gives
10/19/1900, not 284 as it should.

Just change the format to General or Number.

First, 7/12/2012 minus 5/1/2013 is 293, not 284. And 10/19/1900 does indeed
correspond to the date serial number 293 (i.e. 293 days after 12/31/1899, as
Excel counts it; Excel thinks 1900 is a leap year).

As to why Excel displayed the result as Date instead of General or Number
only in C31, who knows?

It did not happen in my test. So I suspect the cell had been formatted as
Date before you entered the formula. Anyway, these things happen all the
time: Excel tries to be helpful and intuits what format to use; but
sometimes it is wrong. We just have to make adjustment sometimes.

 

[JBrewster]

Just change the format to General or Number.

First, 7/12/2012 minus 5/1/2013 is 293, not 284. And 10/19/1900 doe
indeed
correspond to the date serial number 293 (i.e. 293 days afte
12/31/1899, as
Excel counts it; Excel thinks 1900 is a leap year).

As to why Excel displayed the result as Date instead of General o
Number
only in C31, who knows?

It did not happen in my test. So I suspect the cell had been formatte
as
Date before you entered the formula. Anyway, these things happen al
the
time: Excel tries to be helpful and intuits what format to use; but
sometimes it is wrong. We just have to make adjustment sometimes.

I appreciate your help. When one has been using a program a lot one find
little things like different formatting, etc., that might make
difference. What I did do was to go to a column separated from thes
three, set up the first cell as listed in A31, the next cell as B31, an
used the formula in the next cell to give me the correct result I neede
-- I may have had the wrong date in my note for the spreedsheet showed 28
-- 284, copied that and pasted it into C31 and it took. Anyway, you
comments helped me to know something else to look for. Thanks so much.
also thought I had put into my profile, or whereever, that I would b
notified by e-mail when a reply had been made, and I did not receive th
notification. I just thouht I would check. Again thanks