Formula to calculate the last day of the year based on another date column
![Natalia Kataoka](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
- Smartsheet Customer Resources
- 61.4K Get Help
- 325 Global Discussions
- 185 Industry Talk
- 419 Announcements
- 4.2K Ideas & Feature Requests
- 127 Brandfolder
- 154 Just for fun
- 124 Community Job Board
- 441 Show & Tell
- 26 Member Spotlight
- 1 SmartStories
- 278 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!