EOMONTH Formula Changed due to New Year

[reneev]

I have the following formula in a report which takes the hire date (3/15/16) and converts it to the end of the month of the current year (3/31/17) which is when their next annual appraisal would be due. Works great....

=EOMONTH(DATE(YEAR(TODAY()),MONTH($J4),DAY($J4)),0)

HOWEVER, now that it's 2018, it's converting that date to 3/31/18. This is a 2017 report and I need it to keep its 2017 dates. How do I edit this formula to refer to a specific year and not "Today".

In the future, I will copy/paste values to remove the formula when I'm finished with my report to preserve the dates.

 

[Marcílio_Lobão]

reneev, Good afternoon.

Try to use: =EOMONTH(DATE(2017,MONTH($J4),DAY($J4)),0)

Is that what you want?
I hope it helps.

 

[reneev]

YES! Thank you! I couldn't figure out where to put the 2017 and then what to do with the commas and parenthesis.

 

[Marcílio_Lobão]

Glad to have helped you.

Have a nice weekend!

 

[barry houdini]

You could do this more simply (and avoid problems with leap years) by using this formula

=DATE(2017,MONTH($J4)+1,0)

 

[jimrward]

or expanding it a bit further so you don't have to make massive changes for next year try the following

in cell A1 of your report put the year

and use an absolute reference to the YEAR cell in your formula so when you copy it for next year make the copy and change the YEAR

=EOMONTH(DATE($A$1,MONTH($J4),DAY($J4)),0)