IF and DATEVALUE

[Tashat]

Hi...I know I'm not using the correct formula and I wondered if someone could help me, because it is driving me insane.

I need to work out, IF A1 (01/01/05) is greater than or equal to B1 (date) and A1 is less than or equal to C1 (date), then divide D1 (currency) by 12, else = 0. My formula is...

=IF(DATEVALUE($A$1)>=DATEVALUE(b1),IF(DATEVALUE($A$1)<=DATEVALUE(C1),D1/12,0),0)

Can anyone tell me what is wrong with this formula? I'm not sure if I should be using DATEVALUE at all? Or should I have the word AND in there somewhere?

 

[Todd Bardoni]

=IF(AND(A1>=B1,A1<=C1),D1/12,0)

 

[Domenic]

If your dates are true date values, there's no need to use the DATEVALUE function. Try the following...

=IF((A1>=B1)*(A1<=C1),D1/12,0)

Hope this helps!

 

[Tashat]

That's brilliant! Thank you so much..