Count number of days in date range that fall in current week
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!
Best Answer
-
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()))))
Answers
-
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()))))
-
I put this into my project plan and it looks to have worked perfectly! Thanks so much @MCorbin !
-
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()))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!