Solved Excel 2010 IF function with Dates

November 16, 2012 at 10:16:29
Specs: Windows 7

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

I 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 LD

However, my formula gives me Post LD when it should be PE.

What am I doing wrong?


See More: Excel 2010 IF function with Dates

Report •


✔ Best Answer
November 16, 2012 at 12:37:04

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 1st

So, 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)



#1
November 16, 2012 at 11:33:08

Your formula, as shown, simply returns FALSE for me.

MIKE

http://www.skeptic.com/


Report •

#2
November 16, 2012 at 11:38:34

See if this works for you:

=IF(AND(I2>=P2,I2<=Q2),O2,IF(I2<P1,O1,IF(I2>P3,O3,"")))

MIKE

http://www.skeptic.com/


Report •

#3
November 16, 2012 at 12:17:03

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.


Report •

Related Solutions

#4
November 16, 2012 at 12:20:00

Ok, I got it to work with.

=IF(I2>=$P$3,$Q$3,IF(I2>=$P$2,$O$2,$O$1))

Thanks!


Report •

#5
November 16, 2012 at 12:28:17

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.


Report •

#6
November 16, 2012 at 12:37:04
✔ 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 1st

So, 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)


Report •


Ask Question