# Excel IF AND formula and more

Discussion in 'Business Applications' started by holymoly, Jan 31, 2013.

Not open for further replies.

Joined:
Jun 21, 2003
Messages:
635
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#

File size:
10.3 KB
Views:
45
2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
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

Joined:
Jun 21, 2003
Messages:
635
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.

#### Attached Files:

• ###### Tech test2.xlsx
File size:
10.6 KB
Views:
26

Joined:
Jun 21, 2003
Messages:
635
=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 ?

5. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
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",""))))

#### Attached Files:

• ###### Tech test2_etaf.xlsx
File size:
10.7 KB
Views:
43

Joined:
Jun 21, 2003
Messages:
635
Thanks Wayne, again, never would have done it without you.

7. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
your welcome thanks for letting us know

Joined:
Jun 21, 2003
Messages:
635
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?

File size:
10.8 KB
Views:
33
9. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
the inverted commas on the 1st two overdue statements are not correct
slanted rather than
"

i just edited the spreadsheet = see attached

#### Attached Files:

• ###### Tech test2_etaf (2).xlsx
File size:
10.5 KB
Views:
36

Joined:
Jun 21, 2003
Messages:
635
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.

File size:
10.5 KB
Views:
28
11. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
yep, different character as far as excel is concerned

i'll look

Joined:
Jun 21, 2003
Messages:
635
I attached the file.

Look at C3, C4

13. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
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

#### Attached Files:

• ###### Tech test2_etaf (3).xlsx
File size:
11.3 KB
Views:
37

Joined:
Jun 21, 2003
Messages:
635

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.

15. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
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

File size:
20.2 KB
Views:
32

As Seen On

### Welcome to Tech Support Guy!

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

over 733,556 other people just like you!