Only excluding the first weekend

Options
edited 12/09/19

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!

• ✭✭✭✭✭✭
Options

You could set up a table similar to this:

.

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

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.

.

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!