Excel If then else support

Vito DiMercurio
New Contributor

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.


Lets say I have a date in cell A1 and a value in A2. Cell A3 calculates a new date based on A1 minus A2. what I would like is to then say - If A3 is Saturday, subtract 1 more day, If A3 is a Sunday, add 1 more day. Then Highlight the cell so I know that the additional math adjustment was made. I know that I can use WEEKDAY = 7 to find Saturday, and WEEKDAY = 1 to find Sunday but putting it all together has proven difficult.  thanks for the help.

7 Replies

Re: Excel If then else support

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


Re: Excel If then else support

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

Re: Excel If then else support

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)





Re: Excel If then else support



you can use this formula in cell A3




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 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?


Re: Excel If then else support

Perhaps no practical sense, just my exercise with "no IF" formula



Re: Excel If then else support

Ingeborg - this is brilliantly elegant. Thank you so much. Exactly what I was trying to do.

Re: Excel If then else support

For the Add/Subtract formula - may I suggest the following:

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel


Related Conversations
Copy/paste no longer working in Excel
Jon Firooz in Excel on
65 Replies
Security Warning: Automatic update of links has been disabled.
Mark Adams in Excel on
6 Replies
Mark 777 in Excel on
47 Replies
2 Way Cells
Ryan Dolinar in Excel on
14 Replies