SOLVED

Add Years to a date (Date Year function not working)

Steel Contributor

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?  

 

4 Replies

@Tony2021 

here is a file. 

best response confirmed by Tony2021 (Steel Contributor)
Solution

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

1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

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

View solution in original post