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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!