Only excluding the first weekend

vvvriska
vvvriska
edited 12/09/19 in Formulas and Functions

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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]@rowIF(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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!