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

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?

Best Answer

  • Sarah Keortge
    Sarah Keortge ✭✭✭✭
    Answer ✓

    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)

Answers

  • Sarah Keortge
    Sarah Keortge ✭✭✭✭
    Answer ✓

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!