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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!