task days in a year

khause

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!

Isaac Jose

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/2476491-create-efficient-formulas-with-at-cell#row
• Create and Edit Formulas https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

Kind regards,

Isaac

Smartsheet Support

khause

thanks!  I ended up building something that does a similar approach with lots of "if" statements, but this is actually cleaner.  appreciate the help!