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
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#

A sample spreadsheet is attached.
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,435
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
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.
 

Attachments

holymoly

Thread Starter
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 ?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,435
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

holymoly

Thread Starter
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?
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,435
the inverted commas on the 1st two overdue statements are not correct
slanted rather than
"

i just edited the spreadsheet = see attached
 

Attachments

holymoly

Thread Starter
Joined
Jun 21, 2003
Messages
635
A slant can make a difference? :eek:

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

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,435
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

holymoly

Thread Starter
Joined
Jun 21, 2003
Messages
635
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
Joined
Oct 2, 2003
Messages
65,435
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

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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

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.

Join over 807,865 other people just like you!

Latest posts

Top