Count the number of days between two dates by year

Alan Rappa

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.

Paul Newcome

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)))

Donovan

Great - can this be done with NETWORKDAYS? Thanks 👍️