Excel If then else support

[Vito DiMercurio]

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.

[#2]

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

[#3]

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

[#4]

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 

[#5 · Accepted solution]

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?

[#6]

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})

[#7]

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

[#8]

@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