Forum Discussion

Copper Contributor

Oct 24, 2017

Solved

Excel If then else support

Hey everyone, I'm having trouble finding the right syntax for an excel if/else/than formula in a spread sheet. I think I have all the individual parts, but putting it together is stumping me. Let...

excel

IngeborgHawighorst
Oct 25, 2017
Hello,

you can use this formula in cell A3

=A1-A2+IF(WEEKDAY(A1-A2)=1,1,IF(WEEKDAY(A1-A2)=7,-1,0))

Then you can use conditional formatting on cell A3 with a rule that uses a formula. Either use one rule for both Saturday and Sunday adjustments with the formula

=or(WEEKDAY(A1-A2)=1,WEEKDAY(A1-A2)=7)

or use one rule for Sunday with one color, and another rule for Saturday with another colour.
=WEEKDAY(A1-A2)=1 -- sunday rule, use green
=WEEKDAY(A1-A2)=7 -- saturday rule, use red

Does that help?

Yury Tokarev

Iron Contributor

Oct 24, 2017

Hi Vito,

you can add an extra cell in D1 to evaluate if A1 value is Saturday or Sunday, and return -1 or 1 respectively. Your formula in D1 can be =IF(WEEKDAY(A1,1)=7,-1,IF(WEEKDAY(A1=1,1),0)). If you are on Office365, a better alternative would be to use the SWITCH function. In this case, your formula in D1 would be =SWITCH(WEEKDAY(A1,1),7,-1,1,1).

Subsequently, you can add the D1 reference to C1, so that the latter has '=A1-B1+D1', then conditionally format it based on the value in D1 (e.g. colour cell if D1 is not equal 0). Please see attached for an example.

Hope this helps

Yury

Conditionally add or subtract day.xlsx9 KB

SergeiBaklan
Diamond Contributor
Oct 25, 2017
Yury, IMHO, that's resulting date shall be checked on Sat/Sun, not the start date
- Yury Tokarev
  Iron Contributor
  Oct 25, 2017
  Hi Sergei,
  
  thanks for pointing this out. Vito, to evaluate the resulting date, you can just add a reference to B1 in your D1 formula, so that it looks as follows:
  
  Nested IFs: =IF(WEEKDAY(A1+B1,1)=7,-1,IF(WEEKDAY(A1+B1,1)=1,1,0))
  SWITCH: =SWITCH(WEEKDAY(A1+B1,1),7,-1,1,1)
  
  Thanks
  Yury