SOLVED
Home

Excel If then else support

Highlighted
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

Yury

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)

 

Thanks

Yury 

Solution

Re: Excel If then else support

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?

 

Re: Excel If then else support

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

=A1-A2+LOOKUP(MOD((A1-A2)/7,1)*7,{0,0.9,1.5},{-1,1,0})

 

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

@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

 

Related Conversations
EXCEL DO NOT SHOW GRAPH MAP CHART
Mark 777  in  Excel  on
43 Replies
Complex INDEX MATCH - Need assistance
Monica Bossert  in  Formulas and Functions  on
30 Replies
Missing Excel 2013 worksheet tabs
Mike Hill  in  Excel  on
7 Replies
Excel filter issues
P DS  in  Excel  on
13 Replies