SOLVED

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

Steel Contributor

# 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?

4 Replies

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

here is a file.

best response confirmed by Tony2021 (Steel Contributor)
Solution

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

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

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

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.

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

@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

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

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