# Excel IF AND formula and more

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### holymoly

Thread Starter
I currently have a formula that takes the current day, and compares to an Order Picked date.

The formula calculates is the difference between the dates is more than 2.5, "Overdue" is returned in the cell

The problem I encounter is on Mondays and Tuesdays, where I need to change the formula manually to 4.5 and 3.5
because the Sat and Sunday are not skipped and no orders are sent as those are off days. So an order picked on Friday
would show as Overdue on Monday as it is more than 2.5 days.

Original Formula : =IF((A2+2.5)<\$C\$3,"Overdue",IF((A2)>\$C\$3,"Future",""))

The new Formula: =IF(AND(\$D\$3=Monday,A2+4.5<\$C\$3),Overdue, IF(AND(\$D\$3=Tuesday,A2+3.5<\$C\$3),Overdue, IF((A2+2.5)<\$C\$3,"Overdue",IF((A2)>\$C\$3,"Future",""))))

has not worked. Perhaps wrongly positioned parenthesis? It returns #NAME#

A sample spreadsheet is attached.

#### Attachments

• 10.3 KB Views: 45

#### etaf

Wayne
Moderator
you do an IF for weekday
=WEEKDAY(A2,2) and if that returns a 6 or 7 then its a weekend

so for monday =1 ,tuesday =2

The new Formula: =IF(AND(WEEKDAY(\$D\$3,2)=1,A2+4.5<\$C\$3),&#8221;Overdue&#8221;, IF(AND(WEEKDAY(\$D\$3,2)=2,A2+3.5<\$C\$3),&#8221;Overdue&#8221;, IF((A2+2.5)<\$C\$3,"Overdue",IF((A2)>\$C\$3,"Future",""))))

can you post a sample spreadsheet with dummy data - as not sure if you have the date in \$D\$3 or the day of the week

#### holymoly

Thread Starter
OK, I did attach previously, but will again with the update. Returning #value#.

I'm sure monday=1 needs to be defined first

Never heard of the WEEKDAY function till now.

#### Attachments

• 10.6 KB Views: 26

#### holymoly

Thread Starter
=IF(AND(WEEKDAY(\$D\$3,2)=1,A2+4.5<\$C\$3),Overdue, IF(AND(WEEKDAY(\$D\$3,2)=2,A2+3.5<\$C\$3),Overdue, IF((A2+2.5)<\$C\$3,"Overdue",IF((A2)>\$C\$3,"Future",""))))

OK, change \$D\$3 to

SC\$3 ?

#### etaf

Wayne
Moderator
sorry , I missed the spreadsheet attachment dah!!

so I have modified the formula to use today() and so you dont need cell C, D, G or H - its all calculated in the IF statement - see attached

=IF(AND(WEEKDAY(TODAY(),2)=1,A2+4.5<TODAY()),Overdue, IF(AND(WEEKDAY(TODAY(),2)=2,A2+3.5<TODAY()),Overdue, IF((A2+2.5)<TODAY(),"Overdue",IF((A2)>TODAY(),"Future",""))))

#### Attachments

• 10.7 KB Views: 43

#### holymoly

Thread Starter
Thanks Wayne, again, never would have done it without you. #### etaf

Wayne
Moderator
your welcome thanks for letting us know

#### holymoly

Thread Starter
Seems the formula not acting correctly, though it appears so.

Look at B3, C3. According to dictates of formula, they should not return Overdue.

maybe the weekday function for Monday is not being recognized.

See attached.

If you look down Col B, older items that should be overdue, return #NAME.

We need some fine tuning?

#### Attachments

• 10.8 KB Views: 33

#### etaf

Wayne
Moderator
the inverted commas on the 1st two overdue statements are not correct
slanted rather than
"

i just edited the spreadsheet = see attached

#### Attachments

• 10.5 KB Views: 36

#### holymoly

Thread Starter
A slant can make a difference? OK, that said, the second and third fomulae, B3 and B4, the result should be blank cell, not Overdue.

B3 rationale Feb 8 + 4.5 = 12.5 . this is not less than Today which is Feb 11

B4 Feb 7 + 4.5 = 11.5, this is not less than Today which is Feb 11

Please see C3, C4. They correctly return FALSE.

Something wrong with formula, that it returns Overdue.

#### Attachments

• 10.5 KB Views: 28

#### etaf

Wayne
Moderator
A slant can make a difference?
yep, different character as far as excel is concerned

i'll look

#### holymoly

Thread Starter
I attached the file.

Look at C3, C4

#### etaf

Wayne
Moderator
looks like we have something wrong in the tests for TRUE

here are the tests
Code:
``````AND(WEEKDAY(TODAY(),2)=1,A3+4.5<TODAY())
AND(WEEKDAY(TODAY(),2)=2,A3+3.5<TODAY())
(A3+2.5)<TODAY()
(A3)>TODAY()``````
and the results are
FALSE
FALSE
TRUE
FALSE

so have we got this wrong
(A3+2.5)<TODAY()

should it be
(A3+2.5)>TODAY()

does that make sense?

Code:
``=IF(AND(WEEKDAY(TODAY(),2)=1,A32+4.5<TODAY()),"Overdue", IF(AND(WEEKDAY(TODAY(),2)=2,A32+3.5<TODAY()),"Overdue", IF((A32+2.5)<TODAY(),"Overdue",IF((A32)>TODAY(),"Future",""))))``
see row 32 on attached spreadsheet

#### Attachments

• 11.3 KB Views: 37

#### holymoly

Thread Starter
Thanks for your response.

The test results in cells B24 and B25 are correct, they return FALSE.

Therefore Cells B42, B43 should return blank, but they return Overdue.
This is puzzling since they failed the test for Overdue.

#### etaf

Wayne
Moderator
i have broken the whole thing out
so you can see each test and the result true false and also the date the formula is creating

i have added some conditional format to the B column
overdue = red
future = green
blank - fill grey

also columns D, E, F, G
has conditional format fill for true and false

D,E,F,G
has each of the tests - true and false - so we can see whats happening for each test

I, J shows the 2 parts of test1
L, M shows the 2 parts of test2
O shows test3
Q shows test4

so in the logic you require - where is it actually going wrong - what part of the test

should be able to see from the breakdown

to get a blank all 4 tests need to return a FALSE
future will only be applied if TEST4 is TRUE

its going to change each day , and due to our time zones , we may not see the same cells

but you posted on tuesday

at the moment tuesday 12th
it show the blank result against 10, 11 & 12th Feb

so you want the dates 8 and 9th to also be blank
which means test 2 and test 3 needs to change as they are showing TRUE and so will return "overdue"

we can change each section of the tests to get the correct result you need now

Remember nested IFs work through the sequence in order , and i have set up for a true result to show overdue/future

IF TEST1 = TRUE then overdue formula stops - IF FALSE move to TEST2
IF TEST2 = TRUE then overdue formula stops - IF FALSE move to TEST3
IF TEST3 = TRUE then overdue formula stops - IF FALSE move to TEST4
IF TEST4 = TRUE then future formula stops - IF FALSE then show a blank cell - a blank cell only occurs when all 4 tests are FALSE

#### Attachments

• 20.2 KB Views: 32
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 807,865 other people just like you!