Forum Discussion

Vito DiMercurio's avatar
Vito DiMercurio
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's avatar
    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?

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 25, 2017

Yury, IMHO, that's resulting date shall be checked on Sat/Sun, not the start date

Yury Tokarev's avatar
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 

Resources