Excel formula for dates
I am using this formula today and the referenced date in I2 is 4/30/21, so the formula should say 2, but it says FALSE. Can anyone help?
=IF(I2="","",IF(AND(I2=TODAY()-1,I2<=TODAY()-7),4,IF(AND(I2>=TODAY()-8,I2<=TODAY()-14),3,IF(AND(I2>=TODAY()-15,I2<=TODAY()-21),2,IF(AND(I2>=TODAY()-22,I2<=TODAY()-28),1)))))
=IF(I2="","",IF(AND(I2=TODAY()-1,I2<=TODAY()-7),4,IF(AND(I2>=TODAY()-8,I2<=TODAY()-14),3,IF(AND(I2>=TODAY()-15,I2<=TODAY()-21),2,IF(AND(I2>=TODAY()-22,I2<=TODAY()-28),1)))))
Ron Malmstead
Can you explain exactly what you're trying to do? None of the conditions are met, which is why it says false.
As Ron stated, none of the conditions are met. Using this formula, to get a 2, these 2 conditions must be met:
If 4/30/2021 is more than or equal to 5/4/2021 and 4/30/2021 is less than or equal to 4/28/20201 then 2.
Although the first condition is met, the second one is not.
Paul
If 4/30/2021 is more than or equal to 5/4/2021 and 4/30/2021 is less than or equal to 4/28/20201 then 2.
Although the first condition is met, the second one is not.
Paul
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Four conditions:
1) IF(AND(I2=TODAY()-1,I2<=TODAY()-7),4,
2) IF(AND(I2>=TODAY()-8,I2<=TODAY()-14),3,
3) IF(AND(I2>=TODAY()-15,I2<=TODAY()-21),2,
4) IF(AND(I2>=TODAY()-22,I2<=TODAY()-28)
Condition 1 can never happen, the Date cannot equal yesterday AND be earlier than one week prior
Condition 2 can never happen, later than today - 8 (last week) AND earlier than today -14 (two weeks ago)
Condition 3 can never happen, later than today - 15 (three weeks ago) AND earlier than today -21 (four weeks ago)
Condition 4 can never happen, later than four weeks ago AND earlier than five weeks ago.
You have the >= and <= the wrong way round, try:
=IF(I2="","",IF(AND(I2<=TODAY()-1,I2>=TODAY()-7),4,IF(AND(I2<=TODAY()-8,I2>=TODAY()-14),3,IF(AND(I2<=TODAY()-15,I2>=TODAY()-21),2,IF(AND(I2<=TODAY()-22,I2>=TODAY()-28),1)))))
1) Earlier than today-1 and later than today -7 (in previous week)
2) Earlier than today-8 and later than today -14 (two weeks ago)
3) Earlier than today-15 and later than today -21 (three weeks ago)
4) Earlier than today-22 and later than today -28 (four weeks ago)
What if the date is more than four weeks ago, you will still get a FALSE.
1) IF(AND(I2=TODAY()-1,I2<=TODAY()-7),4,
2) IF(AND(I2>=TODAY()-8,I2<=TODAY()-14),3,
3) IF(AND(I2>=TODAY()-15,I2<=TODAY()-21),2,
4) IF(AND(I2>=TODAY()-22,I2<=TODAY()-28)
Condition 1 can never happen, the Date cannot equal yesterday AND be earlier than one week prior
Condition 2 can never happen, later than today - 8 (last week) AND earlier than today -14 (two weeks ago)
Condition 3 can never happen, later than today - 15 (three weeks ago) AND earlier than today -21 (four weeks ago)
Condition 4 can never happen, later than four weeks ago AND earlier than five weeks ago.
You have the >= and <= the wrong way round, try:
=IF(I2="","",IF(AND(I2<=TODAY()-1,I2>=TODAY()-7),4,IF(AND(I2<=TODAY()-8,I2>=TODAY()-14),3,IF(AND(I2<=TODAY()-15,I2>=TODAY()-21),2,IF(AND(I2<=TODAY()-22,I2>=TODAY()-28),1)))))
1) Earlier than today-1 and later than today -7 (in previous week)
2) Earlier than today-8 and later than today -14 (two weeks ago)
3) Earlier than today-15 and later than today -21 (three weeks ago)
4) Earlier than today-22 and later than today -28 (four weeks ago)
What if the date is more than four weeks ago, you will still get a FALSE.