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?

     

Michael_Avidan's avatar
Michael_Avidan
MVP
Nov 02, 2017

@Vito,
For the Add/Subtract formula - may I suggest the following:
=A1-A2+CHOOSE(WEEKDAY(A1-A2),1,,,,,,-1)

--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

 

Resources