Count the number of days between two dates by year
Hello my fellow smartsheeters,
Working on a current task for our finance team and scratching my head at this one.
The end goal is to track spend by year for roadmap projects.
So, if project A starts in 2021 and runs to 2023, how is the total project investment allocated between 2021, 2022 and 2023.
I started out by figuring out the duration of the project (number of days between start date and end date) then, figuring out the number of days from the start date to the end of the year (2022).
Then, I can subtract that total from the total number of days to determine the split between 2022/2023.
All well and good, but the curveball is that some projects may have started in 2021 and go to 2024 which is why I'm making weird faces as my computer (and finance team) right now.
TLDR: Can you break down the number of days between two dates by the years they fall in?
Thanks in advance.
Answers
-
You would want separate columns for each year (year 1, 2, 3, 4).
Year 1:
=IF(YEAR([End Date]@row) = YEAR([Start Date]@row), [End Date]@row - [Start Date]@row, DATE(YEAR([Start Date]@row), 12, 31) - [Start Date]@row)
Year 2:
=IF(YEAR([End Date]@row) = YEAR([Start Date]@row) + 1, [End Date]@row - DATE(YEAR([End Date]@row), 1, 1), IF(YEAR([End Date]@row) > YEAR([Start Date]@row) + 1, DATE(YEAR([Start Date]@row) + 1, 12, 31) - DATE(YEAR([Start Date]@row) + 1, 1, 1)))
Year 3:
=IF(YEAR([End Date]@row) = YEAR([Start Date]@row) + 2, [End Date]@row - DATE(YEAR([End Date]@row), 1, 1), IF(YEAR([End Date]@row) > YEAR([Start Date]@row) + 2, DATE(YEAR([Start Date]@row) + 2, 12, 31) - DATE(YEAR([Start Date]@row) + 2, 1, 1)))
Year 4:
=IF(YEAR([End Date]@row) = YEAR([Start Date]@row) + 3, [End Date]@row - DATE(YEAR([End Date]@row), 1, 1), IF(YEAR([End Date]@row) > YEAR([Start Date]@row) + 3, DATE(YEAR([Start Date]@row) + 3, 12, 31) - DATE(YEAR([Start Date]@row) + 3, 1, 1)))
-
Great - can this be done with NETWORKDAYS? Thanks 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!