If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula adding 2 days...
So, here is the formula I'm using. Some cells it's adding 2 or 3 days to the
MONTH and some cells it's not. I don't WANT it to add 2 days. It should just be adding 5 years and leaving the month and day the same... what is happening here? =IF(J5="","",DATE(YEAR(J5)+5,MONTH(J5),DAY(J6))) |
#2
|
|||
|
|||
Formula adding 2 days...
Never mind!!! I'm ridiculous! I figured it out!!!!
"Zoe" wrote: So, here is the formula I'm using. Some cells it's adding 2 or 3 days to the MONTH and some cells it's not. I don't WANT it to add 2 days. It should just be adding 5 years and leaving the month and day the same... what is happening here? =IF(J5="","",DATE(YEAR(J5)+5,MONTH(J5),DAY(J6))) |
#3
|
|||
|
|||
Formula adding 2 days...
Not all years have the same number of days:
Leap year = 366 days Not leap year = 365 days Not all months have the same number of days: Jan = 31 days Feb = 28 or 29 days June = 30 days So, when you add years and, depending on what day is in J6, Excel adjusts the resulting accordingly. A couple of simple examples: A1 = 2/29/2008 =DATE(YEAR(A1)+2,MONTHA1),DAY(A1)) That returns the date 3/1/2010 You want to add 2 years to the date but 2010 is not a leap year and Feb 29 2010 is not a valid date so Excel returns the next valid date, 3/1/2010. A1 = 8/31/2010 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) That returns the date 10/1/2010 You want to add 1 month to the date but Sept only has 30 days and 9/31/2010 is not a valid date so Excel returns the next valid date 10/1/2010. Consider this formula: A1 = 1/1/2010 =DATE(YEAR(A1),MONTH(A1),35) In essence, Excel is "smart" enough to know that January 35th 2010 is really February 4th 2010. You might be able to use the EDATE function to get the results you expect. A1 = 2/29/2008 =EDATE(A1,60) returns 2/28/2013 Format as Date. That will return the date 5 years from the date in A1. (12 months per year * 5 years = 60 months) =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) returns 3/1/2013 -- Biff Microsoft Excel MVP "Zoe" wrote in message ... So, here is the formula I'm using. Some cells it's adding 2 or 3 days to the MONTH and some cells it's not. I don't WANT it to add 2 days. It should just be adding 5 years and leaving the month and day the same... what is happening here? =IF(J5="","",DATE(YEAR(J5)+5,MONTH(J5),DAY(J6))) |
Thread Tools | |
Display Modes | |
|
|