Only excluding the first weekend
Hi,
I'm currently using a formula to spit out a certain number of days between two dates. I want to exclude the first weekend if it falls within the first 3 days, but otherwise include it.
Currently to exclude weekends entirely I'm using
=NETWORKDAYS([SALE DATE]21, [OUTPUT DATE]21) - 4
I'd like the formula to work like this:
If SALE DATE = Wednesday the 14th of August,
and OUTPUT DATE = Wednesday the 28th of August,
I want it to display 12 days, it excludes the first weekend because it was within 3 days of the start date, but counts the second one.
Thank you for any help in advance!
Comments
-
You could set up a table similar to this:
.
Weekday Adjustment
1 1
2 0
3 0
4 2
5 2
6 2
7 2
.
If you run an INDEX/MATCH on this table to pull from the Adjustment column based on the weekday of the start date like this...
=INDEX(Adjustment:Adjustment, MATCH(WEEKDAY([Sale Date]@row, Weekday:Weekday, 0))
It will return a numerical value. So if the Sale Date is a Wednesday, Thursday, Friday, or Saturday, it will return a 2. Sunday would return a 1, and Monday and Tuesday would both return a 0 (zero).
Basically since the weekend falls within the first 3 days of the sale date for Wednesday, Thursday, and Friday, Those are the 3 weekdays you want to subtract 2 for the first weekend on.
So we take this INDEX/MATCH that is returning a number of days and subtract this from your calculations.
=[Output Date]@row - [Sale Date]@row - INDEX(Adjustment:Adjustment, MATCH(WEEKDAY([Sale Date]@row, Weekday:Weekday, 0))
.
This can also be accomplished using an IF/OR statement.
=[Output Date]@row - [Sale Date]@row - IF(OR(WEEKDAY([Sale Date]@row) = 4, WEEKDAY[Sale Date]@row) = 5, WEEKDAY([Sale Date]@row) = 6)), 2, 0)
Which basically says if the WEEKDAY of the [Sale Date] is 4, 5, or 6 (Wed, Thur, or Fri), subtract 2, otherwise subtract 0 (zero).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!