Need to calculate advance per week with a range of two dates.
Dear all,
I need to support to determine a formula that allows me To perform a weekly count of how many days (or if If any) will happen this week based on range of between 2 dates.
Example
Today is 3/19/2020
Start Date End Date
3/16/2020 04/02/20
This week will have a count = to 5 days from within that range. (3/16 - 3/20)
Next week they will have another 5 (3/23 - 3/27)and so on until we reach the end date's week when will have only 4 days that week (3/30 - 4/2 since that day is a Thursday)
04/23/20 04/27/20
This range will have zero days this current week since dates are on the future
03/11/20 03/12/20
This range will have Zero since the dates are in the past.
Thank you!
Answers
-
Hi Maria,
Would you be able to post a screen capture of how your sheet is set up? (Blocking out any sensitive data).
You could use a NETWORKDAYS function to calculate the days between specific dates, see here: https://help.smartsheet.com/function/networkdays
The NETWORKDAYS function doesn't include weekends, which it sounds like is what you're looking for. However, it would span across your whole selection, so if you input the Start Date and End Date for that first date range, it would give you the total working days without breaking it down into the different weeks:
=NETWORKDAYS([Start Date]@row, [End Date]@row)
We could then build on that, say to minus off the number of Networkdays between the Start Date and Today, but it would be helpful to see how your sheet is built, and where you want the day count to be returned, before creating formulas.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!