Count number of days in date range that fall in current week

Eric James

Hi everyone,

I have a number of project plans in Smartsheet and am trying to get a summary of how many task hours each person has assigned for the current week.

The plans currently capture task Start Date, End Date, and number of hours required to complete the task.

As some tasks will take multiple weeks to complete, I need a way to calculate how any days within a date range fall in the current week.

(i.e. Start Date = 08/16 & End Date = 08/27; Total workdays = 10; Total workdays in current week = 5)

I cannot seem to figure out a way to do this. Any help would be greatly appreciated!

MCorbin

I created helper columns to determine 1) if the date range includes the current week and 2) to set the start and finish dates if the date range isn't entirely within the current week. Then I used those helper columns to calculate the # of days in the current week:

Formulas:

Start Date or Sunday: =IF(OR([Start Date]@row > TODAY(7 - WEEKDAY(TODAY())), [End Date]@row < TODAY(1 - WEEKDAY(TODAY()))), "", IF([Start Date]@row <= TODAY(1 - WEEKDAY(TODAY())), TODAY(1 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Saturday: =IF(ISDATE([Start Date or Sunday]@row), IF(AND([End Date]@row >= TODAY(1 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(7 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(7 - WEEKDAY(TODAY()))))

Formula for # of Days this week: =IF(ISDATE([Start Date or Sunday]@row), NETDAYS([Start Date or Sunday]@row, [End Date or Saturday]@row), 0)

If you only wanted Monday - Friday, you'd modify the first 2 formulas like this:

Start Date or Monday: =IF(OR([Start Date]@row > TODAY(6 - WEEKDAY(TODAY())), [End Date]@row < TODAY(2 - WEEKDAY(TODAY()))), "", IF([Start Date]@row <= TODAY(2 - WEEKDAY(TODAY())), TODAY(2 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Friday: =IF(ISDATE([Start Date or Sunday]@row), IF(AND([End Date]@row >= TODAY(2 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(6 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(6 - WEEKDAY(TODAY()))))

MCorbin

I created helper columns to determine 1) if the date range includes the current week and 2) to set the start and finish dates if the date range isn't entirely within the current week. Then I used those helper columns to calculate the # of days in the current week:

Formulas:

Start Date or Sunday: =IF(OR([Start Date]@row > TODAY(7 - WEEKDAY(TODAY())), [End Date]@row < TODAY(1 - WEEKDAY(TODAY()))), "", IF([Start Date]@row <= TODAY(1 - WEEKDAY(TODAY())), TODAY(1 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Saturday: =IF(ISDATE([Start Date or Sunday]@row), IF(AND([End Date]@row >= TODAY(1 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(7 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(7 - WEEKDAY(TODAY()))))

Formula for # of Days this week: =IF(ISDATE([Start Date or Sunday]@row), NETDAYS([Start Date or Sunday]@row, [End Date or Saturday]@row), 0)

If you only wanted Monday - Friday, you'd modify the first 2 formulas like this:

Start Date or Monday: =IF(OR([Start Date]@row > TODAY(6 - WEEKDAY(TODAY())), [End Date]@row < TODAY(2 - WEEKDAY(TODAY()))), "", IF([Start Date]@row <= TODAY(2 - WEEKDAY(TODAY())), TODAY(2 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Friday: =IF(ISDATE([Start Date or Sunday]@row), IF(AND([End Date]@row >= TODAY(2 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(6 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(6 - WEEKDAY(TODAY()))))

Eric James

I put this into my project plan and it looks to have worked perfectly! Thanks so much @MCorbin !

rread

This was great! Such a help, thank you. I made two adjustments from your samples.

Start Date or Monday:

=IF(OR([Start Date]@row > TODAY(6 - WEEKDAY(TODAY())), ([End Date]@row < TODAY(2 - WEEKDAY(TODAY())))), "", IF([Start Date]@row <= TODAY(2 - WEEKDAY(TODAY())), TODAY(2 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Friday

=IF(ISDATE([Start Date or Monday]@row), IF(AND([End Date]@row >= TODAY(2 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(6 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(6 - WEEKDAY(TODAY()))))