Forum Discussion
Add Years to a date (Date Year function not working)
Hello,
I have a date: 12/31/24 and I need to add 7 years to it.
I use:
=DATE(YEAR(A2)+7,MONTH(0),DAY(0))
but it returns:
12/31/30 and this is wrong.
I use EDATE:
=EOMONTH(A2,7*12)
and it returns:
12/31/31
this is correct.
Why doesnt the DATE function work? Am I using it wrong?
Tony2021 wrote: ``I use EDATE: =EOMONTH(A2,7*12) and it returns: 12/31/31``
That worked only by coincidence because A2 contains the date 12/31/... .
In general, you should indeed EDATE as you said, not EOMONTH -- unless you want the end of the same month 7 years from now.
For example, if A2 is 4/15/2024, EDATE(A2, 7*12) returns 4/15/2031, whereas EOMONTH(A2, 7*12) returns 4/30/2021.
-----
Tony2021 wrote: ``I use: =DATE(YEAR(A2)+7,MONTH(0),DAY(0)) but it returns: 12/31/30 and this is wrong``
Because the parameter to MONTH and DAY is a date.
In Excel, dates are represented by integer "serial numbers". They are the number of days since 12/31/1899, which Excel displays as 1/0/1900 (in MDY form).
(Aside.... But if we type 1/0/1900, Excel does not recognize that as a date.)
So, MONTH(0) is the month number of 1/0/1900, which is 1. And DAY(0) is the day number of 1/0/1900, which 0.
Thus, with any 2024 date in A2, DATE(YEAR(A2)+7,MONTH(0),DAY(0)) is equivalent to DATE(2031,1,0).
And Excel interprets day 0 of any month as the last day of the previous month.
Thus, DATE(2031,1,0) is equivalent to DATE(2030,12,31).
Tony2021 wrote: ``I use EDATE: =EOMONTH(A2,7*12) and it returns: 12/31/31``
That worked only by coincidence because A2 contains the date 12/31/... .
In general, you should indeed EDATE as you said, not EOMONTH -- unless you want the end of the same month 7 years from now.
For example, if A2 is 4/15/2024, EDATE(A2, 7*12) returns 4/15/2031, whereas EOMONTH(A2, 7*12) returns 4/30/2021.
-----
Tony2021 wrote: ``I use: =DATE(YEAR(A2)+7,MONTH(0),DAY(0)) but it returns: 12/31/30 and this is wrong``
Because the parameter to MONTH and DAY is a date.
In Excel, dates are represented by integer "serial numbers". They are the number of days since 12/31/1899, which Excel displays as 1/0/1900 (in MDY form).
(Aside.... But if we type 1/0/1900, Excel does not recognize that as a date.)
So, MONTH(0) is the month number of 1/0/1900, which is 1. And DAY(0) is the day number of 1/0/1900, which 0.
Thus, with any 2024 date in A2, DATE(YEAR(A2)+7,MONTH(0),DAY(0)) is equivalent to DATE(2031,1,0).
And Excel interprets day 0 of any month as the last day of the previous month.
Thus, DATE(2031,1,0) is equivalent to DATE(2030,12,31).
- thank you. I see I cant use "0" and instead must refer to the cell that has a date even if its the same cell as the year cell.
Tony2021wrote: ``must refer to the cell that has a date even if its the same cell as the year cell"
IMHO, EDATE is better than DATE. Consider 7 years from Feb 29 2024. EDATE returns Feb 28 2031. DATE returns Mar 1 2031. Arguably, it's a matter of opinion.
.... Unless you want a specific month/day in the future year. For example, if you always want a year-end date: DATE(7+YEAR(A2), 12, 31).
