Formula for Between Dates (Excel 03)

H

Havenstar

Hi,

I am looking for a formula that does the following. If >=1/04/2009 and <=
1/31/2009 then P10 / If >= 2/1/2009 and <= 2/28/2009 then P11.....

Any assistance is greatly appreciated.

Thank you,
Havenstar
 
B

Bernard Liengme

You did not say what cell holds the date; I will use A1
=IF(and(A1 >=DATEVALUE("1/04/2009",a1 <= DATEVALUE(1/31/2009),P10,IF(and(A1
=DATEVALUE("2/1/2009"),a1 <= DATEVALUE("2/18/2009"),P11,"x")
Click to expand...
You can replace "x" by another IF to seven levels but it will get messy

This seems better =INDIRECT("P"&MONTH(A1)+9)
But it will give P10 for all days in Jan
Fix it with
=IF(A1>DATEVALUE(1/03/2009),INDIRECT("P"&MONTH(A1)+9),"")
best wishes
 
J

JBeaucaire

A few ways to do this. I noticed your formula for January didn't include the
whole month. To use specific dates like that, you could use an IF(AND:

=IF(AND(A10>=DATE(2009,1,4),A10<DATE(2009,2,1)),P10,"not")

But if you can just allow the month alone as a check, you could try this:

=IF(MONTH(A11)=2,P11,"not Feb")
 
H

Havenstar

Thanks JB - I added an = to before the second date and it worked perfeclty.

Havenstar
 
J

JBeaucaire

Careful there, I used Feb 1 as the cutoff, so I believe just a < is correct.
If you manually figure the last day of each month (ugh) then =<Jan 31 would
work, I just thought <Feb1 is less mental gymnastics.

Cheers.
 
Top