10-24-2017 01:27 PM
10-24-2017 01:27 PM
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.
10-24-2017 04:51 PM
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
10-24-2017 05:34 PM
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))
10-24-2017 05:37 PMSolution
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?
10-25-2017 06:06 AM
Perhaps no practical sense, just my exercise with "no IF" formula
11-02-2017 03:03 PM - edited 11-02-2017 03:04 PM
For the Add/Subtract formula - may I suggest the following:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)