turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 119K Members
- 1,021 Online
- 30K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Excel If then else support

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
## Excel If then else support

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Labels:

7 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-24-2017 04:51 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-24-2017 05:14 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-24-2017 05:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-24-2017 05:37 PM

SolutionHello,

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?

Best Response confirmed by Vito DiMercurio (New Contributor)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-25-2017 06:06 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-25-2017 07:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2017 03:03 PM - edited 11-02-2017 03:04 PM

@Vito,

For the Add/Subtract formula - may I suggest the following:**=A1-A2+CHOOSE(WEEKDAY(A1-A2),1,,,,,,-1)**

**--------------------------Michael (Micky) Avidan**

** **

Related Conversations

Add 5 if a cell contains text "yes"

Titchard Family
in
Formulas and Functions
on
11-30-2017
172
Views

0 Likes

6 Replies

IFERROR - HLOOKUP- IF formula error

data24365
in
Formulas and Functions
on
01-12-2018
184
Views

0 Likes

14 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft