IF AND Function to return values depending on where a date falls within 3 ranges.

[Roadrash]

Hi,

I want excel to look at the date value (30-Oct-16) and compare if it falls between 3 date ranges.

First site activation to FPI
FPI to LPFV
LPFV to LPLV

Depending on the result I want excel to insert 0, 0.01 or 0.005 value in the cell. Therefor if the first argument is false it needs to move onto the second argument etc I am using IF (AND) however having trouble combining the 3 arguments
[TABLE="width: 528"]
<colgroup><col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="166" style="width: 125pt; mso-width-source: userset; mso-width-alt: 6070;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;" span="2"> <tbody>[TR]
[TD="class: xl151, width: 127, bgcolor: transparent"] [/TD]
[TD="class: xl149, width: 166, bgcolor: transparent"]First Site Activation[/TD]
[TD="class: xl147, width: 97, bgcolor: transparent"]FPI[/TD]
[TD="class: xl147, width: 118, bgcolor: transparent"]LPFV[/TD]
[TD="class: xl147, width: 97, bgcolor: transparent"]LPLV[/TD]
[TD="class: xl148, width: 97, bgcolor: transparent"]DBL[/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: transparent"]PRIMA[/TD]
[TD="class: xl150, bgcolor: transparent"]5-Apr-16[/TD]
[TD="class: xl146, bgcolor: transparent"]3-Aug-16[/TD]
[TD="class: xl146, bgcolor: transparent"]1-Mar-18[/TD]
[TD="class: xl146, bgcolor: transparent"]1-Mar-20[/TD]
[TD="class: xl146, bgcolor: transparent"]1-Apr-20[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 382"]
<colgroup><col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="166" style="width: 125pt; mso-width-source: userset; mso-width-alt: 6070;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <tbody>[TR]
[TD="class: xl145, width: 127, bgcolor: #4F81BD"]FTE OCT 2016[/TD]
[TD="class: xl145, width: 166, bgcolor: #4F81BD"]FTE NOV 2016[/TD]
[TD="class: xl145, width: 97, bgcolor: #4F81BD"]FTE DEC 2016[/TD]
[TD="class: xl145, width: 118, bgcolor: #4F81BD"]FTE JAN 2017[/TD]
[/TR]
[TR]
[TD="class: xl147, bgcolor: transparent, align: right"]30-Oct-16[/TD]
[TD="class: xl147, bgcolor: transparent, align: right"]30-Nov-16[/TD]
[TD="class: xl147, bgcolor: transparent, align: right"]30-Dec-16[/TD]
[TD="class: xl147, bgcolor: transparent, align: right"]30-Jan-17[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

The syntax could be something like this. Substitute your cell references.

=IF(AND(30-Oct-16>=FPIDate, 30-Oct-16< lpfvdate), 0.01, IF(AND(30-oct-16 >=LPFVDate, 30-Oct-16< lplvdate), 0.005,"both date ranges failed"))

 

[Roadrash]

Thanks.

=IF(AND(A21<=c8),0 IF(AND(A21>= c8,A21<d8), 0.01, IF(AND(A21>=E8, A21<F8), 0.005))

I need to change the formula.

If 30 Oct 16 is earlier than FPI I want it to return 0
If 30 Oct 16 is between FPI and LPLV I want it to return 0.01
If 30 Oct 16 is between LPLV and DBL I want it to return 0.005
If 30 Oct 16 is after DBL I want it to return 0

 

[Roadrash]

sorry formula did not completely copy.

=IF(AND(A21<=C8),0,IF(AND(A21>=C8,A21<=E8),0.1),IF(AND(A21>=E8,A21<=F8),0.005),IF(AND(A21>F8),0))

says I have too many arguments.

 

[AlphaFrog]

<d8), 0.01,="" if(and(a21=""><f8), 0.005))


if 30 oct 16 is earlier than fpi i want it to return 0
if 30 oct 16 is between fpi and lplv i want it to return 0.01
if 30 oct 16 is between lplv and dbl i want it to return 0.005
if 30 oct 16 is after dbl i want it to return 0

=if(and(a21 >=c8, a21< e8),0.1, if(and(a21 >=e8,a21< f8),0.005,0))

 

[Roadrash]

Perfect, thanks. I have even extended it to 3 dates ranges now. Thanks.

Jay