Only excluding the first weekend

vvvriska

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! 

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]@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).