Formula to calculate the last day of the year based on another date column

Natalia Kataoka

I have a sheet that tracks Agreement Execution Dates, and I would like my Agreement Expiration Date column to record the last day of the year of execution (so if executed 5/1/20, expiration would be 12/31/20). How would I write this?

Sarah Keortge

Hi Natalia,

You can use the DATE function which allows you to build a custom date using the 3 date parts of year, month, and day. Since you want the date to be the last day of the year based on the agreement date, you simply need to retrieve the year from the agreement date and then the month and day will be fixed as December 31st:

=DATE(year([Agreement Expiration Date]@row),12,31)

Sarah Keortge

Hi Natalia,

You can use the DATE function which allows you to build a custom date using the 3 date parts of year, month, and day. Since you want the date to be the last day of the year based on the agreement date, you simply need to retrieve the year from the agreement date and then the month and day will be fixed as December 31st:

=DATE(year([Agreement Expiration Date]@row),12,31)