When adding 1 business day to Saturday and Sunday, gives Tuesday instead of Monday

[Anonymous]

Hello, 

I have tried creating calendar columns, where I have specific date and i want to have two additional columns, one to subtract 1 business day and another to add 1 business day. 

When creating a column with subtraction, everything seems well, however, when i add 1 business day it shows wrong date, i.e: 

what i did: 

1. I identified whether the day is workday:

2. I created ranking: 

3. Then I used this formula to add 1 business day:

You may see that if original date is Saturday or Sunday, it gives me Tuesday instead of Monday. 

Any ideas what i did wrong and how could i solve it?? I guess it has something to do with ranking,  i probably need Friday, Saturday and Sunday to have the same rank instead of Saturday, Sunday, Monday...Not sure how to achieve it!

[Greg_Deckler]

@Anonymous Can you explain your logic of what you are trying to accomplish exactly? What should the results be and why?

[Anonymous]

Hi @Greg_Deckler , 

The first column Date would be my actual date when shipment has arrived. Customer gives us "grace period" of +/- day, so i want to have two additional columns

1. Date + 1 business day

2. Date - 1 business day 

I hope this makes sense. 

[Greg_Deckler · Accepted solution]

@Anonymous OK, I would just create 1 column for like this then:

Add1WorkingDay Column =
  SWITCH( WEEKDAY( [Date], 2 ),
    5, [Date] + 3,
    6, [Date] + 2,
    [Date] + 1
  )


Minus1WorkingDay Column =
  SWITCH( WEEKDAY( [Date], 2 ),
    1, [Date] - 3,
    7, [Date] - 2,
    [Date] - 1
  )
   

Or use this fancy version I created a long time ago: Add Working Days - Microsoft Fabric Community

[Anonymous]

@Greg_Deckler thank you so much!  I have no idea why i was trying to do it in such a difficult way when an easy one was in front of me....

Thank you for such a quick help!