Forum Discussion
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.
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?
7 Replies
@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)
ISRAELPerhaps 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})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?
Ingeborg - this is brilliantly elegant. Thank you so much. Exactly what I was trying to do.
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
Yury, IMHO, that's resulting date shall be checked on Sat/Sun, not the start date
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
