task days in a year
been looking for ideas, but is there a simple way to determine the number of days any given project task has within a specific year? some tasks are just within one calendar year and some are across years, and I wanted to calculate the total number of days within each year by row/task.
thanks!
Comments

Hello khause,
Although there isn't any feature built into the app for this, I've created the formula below which should achieve what you're looking for:
=IF(YEAR([Start Date]@row) > 2018, 0, IF(AND(YEAR([Start Date]@row) = 2018, YEAR([Finish Date]@row) = 2018), Duration@row, IF(AND(YEAR([Start Date]@row) < 2018, YEAR([Finish Date]@row) = 2018), (([Finish Date]@row)  (DATE(2018, 1, 1))) / 7 * 5, IF(AND(YEAR([Start Date]@row) = 2018, YEAR([Finish Date]@row) > 2018), ((DATE(2018, 12, 31))  ([Start Date]@row)) / 7 * 5, 0))))
The formula is fairly lengthy but easy to apply to any project sheet with dependencies enabled. I've attached a screenshot of the formula in action below.
This formula checks the Start Date and End Date across the conditions below and returns a value accordingly:
 Task Starts after the target year, return 0
 Task Starts and Ends during the target year, return Duration
 Task Starts before the target year and ends during the target year, subtract January 1st of the target year from the finish date then divide by 7 days per week and multiply by 5 working days per week
 Task Starts during the target year and ends after the target year, subtract the Start Date from December 31st of the target year then divide by 7 days per week and multiply by 5 working days per week
 None of the above returns 0 (the only condition remaining is Start and End before the target year).
Note: If this formula is applied to a row which does not yet have a Start and End date, the formula will return an error "#INVALID DATA TYPE"
To apply this formula to your sheet, I recommend pasting the formula into Microsoft Word or another text editor so that you can Find and Replace the year, working days, and column names.
Replace "2018" with the year you'd like to check. If your work week has anything other than 5 working days, you'll want to replace "7 * 5" with "7 * [insert working days per week here]." You'll also want to ensure that you adjust your column names to match the formula, "Start Date" and "End Date," or replace the column names in the formula with the column names in your sheet.
See the functions and formula information below to understand the syntax:
 IF Function https://help.smartsheet.com/function/if
 AND Function https://help.smartsheet.com/function/and
 YEAR Function https://help.smartsheet.com/function/year
 DATE Function https://help.smartsheet.com/function/date
 Using @rowhttps://help.smartsheet.com/articles/2476491createefficientformulaswithatcell#row
 Create and Edit Formulas https://help.smartsheet.com/articles/2476171createandeditformulasinsmartsheet
Kind regards,
Isaac
Smartsheet Support

thanks! I ended up building something that does a similar approach with lots of "if" statements, but this is actually cleaner. appreciate the help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!