Only excluding the first weekend

edited 12/09/19 in Formulas and Functions


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 



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! 


  • 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).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!