I have a spreadsheet with dates in the format DD-MMM-YYYY (ex: 22-MAR-2012). I want to use an IF function to classify these into 3 different time frames. Pre-Validation <= 20-Feb-2012

PE = 21-Feb-2012 through 30-Apr-2012

Post LD >= 01-May-2012I am using =IF(I2>=$P$2<=$Q$2,$O$2,IF(I2>$P$3,$O$3,IF(I2<=$P$1,$O$1)))

I2 = 22-Mar-2012

P2 = 21-Feb-2012

Q2 = 30-Apr-2012

P3 = 01-May-2012

P1 = 20-Feb-2012

O1 = Pre-Validation

O2 = PE

O3 = Post LDHowever, my formula gives me Post LD when it should be PE.

What am I doing wrong?

✔ Best Answer

I had a typo above. I copied and pasted this one. :) =IF(I2>=$P$3,$O$3,IF(I2>=$P$2,$O$2,$O$1))

P2 = February 21st

P3 = May 1stSo, if my cell is greater than or equal to P3, then it is Post LD (O3)

And, if my cell is greater than or equal to P2, then it is PE (O2)

And then if my cell is neither of those it is Pre-Validation (O1)

See if this works for you: =IF(AND(I2>=P2,I2<=Q2),O2,IF(I2<P1,O1,IF(I2>P3,O3,"")))

MIKE

That's probably because this term will always return FALSE regardless of what is in the cells: I2>=$P$2<=$Q$2

Try it this way:

AND(I2>=$P$2,$P$2<=$Q$2)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ok, I got it to work with. =IF(I2>=$P$3,$Q$3,IF(I2>=$P$2,$O$2,$O$1))

Thanks!

It's interesting how you got it to work without even referenicing the same cells as you did in your original formula. I see no reference to P1 or Q2 in your latest formula, yet you were comparing those cells to other cells in your orginal post.

Like I said...interesting.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I had a typo above. I copied and pasted this one. :) =IF(I2>=$P$3,$O$3,IF(I2>=$P$2,$O$2,$O$1))

P2 = February 21st

P3 = May 1stSo, if my cell is greater than or equal to P3, then it is Post LD (O3)

And, if my cell is greater than or equal to P2, then it is PE (O2)

And then if my cell is neither of those it is Pre-Validation (O1)

Ask Your Question

Weekly Poll

Do you think cellular roaming fees are unfair?

Discuss in The Lounge

Poll History