Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 4
Default Help with determining # of days from certain dates

I need to determine the # of days from several dates for about 2 years past until, say May 1, 2013. 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. The first answer for C1 is 1065, and the numbers change correctly as we go down the rows. However, A31 reads 5/1/2013, B31 reads 7/12/2012, but C31 gives 10/19/1900, not 284 as it should. In "Show Formula", I can see the numbers and subtract them to get 284, but Excel will not allow me to put the numbers in. What is the problem here? There are most likely other ways to get this done, but this is how I tried. OK through 30 rows, but 31 is out of whack. Any suggestions?
Thanks for your help.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 829
Default Help with determining # of days from certain dates

"JBrewster" <jTAKEOUTbrewster wrote:
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.

  #3   Report Post  
Junior Member
 
Posts: 4
Default

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.[/quote]

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

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
DETERMINING NUMBER OF DAYS / MONTHS / YEARS BETWEEN TWO DATES Boyertown Casket[_2_] Excel Programming 1 March 17th 09 06:27 PM
Calc days between two dates and exclude leap year days scoz Excel Worksheet Functions 5 November 23rd 07 04:58 PM
Conditional Formatting Dates calculating 10 days and 30 days from a certain date Sioux[_2_] Excel Worksheet Functions 2 October 11th 07 02:04 PM
Determining the number of specific days between two dates in Excel jon s Excel Worksheet Functions 9 March 21st 05 10:13 PM
Determining number of days between dates Debbie Excel Worksheet Functions 4 January 20th 05 01:07 AM


All times are GMT +1. The time now is 08:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"