Using an IF formula to see if criteria falls within Date Range

[RobinTMathew]

I am trying to create a formula to say that if Claim A has a date of XYZ which falls between range of 01/01/2001-01/01/2007, then F, otherwise NF.

How could we write a range into an if formula?

 

[barry houdini]

If you have the date to test in A2 and the date range in B2 and C2

=IF(AND(A2>=B2,A2<=C2),"F","NF")

 

[RobinTMathew]

Thank you Barry. How about if #N/A is in B2 and C2? In this case I would like for it to read "NF".

Is there a way to incorporate this into the Formula?

 

[rogerclue]

looking for something similar, I have a start and end date, and three Quarters in different columns. I want the formula to check whether the date range is within the quarter, and if, so, to count the number of days that fall into that quarter. Then I was hoping to replicate that across the three columns and just change the date. I have the following formula, but I tried to add something else to it, and then excel spit it back, saying that there were too many formulas in the cell. Any thoughts? (M2 contains the start date, and N2 is the end date). Thanks!

=IF(AND(OR(M2>DATE(2009,10,1), M2<(DATE(2009,12,31))),OR(N2>DATE(2009,10,1), N2<(DATE(2009,12,31)))), (N2-M2+1)-IF(N2>DATE(2009,12,31), N2-DATE(2009,12,31),0)-IF(M2<date(2009,10,1), date(2009,10,1)-m2,0),="" )=""></date(2009,10,1),>

 

[barry houdini]

Thank you Barry. How about if #N/A is in B2 and C2? In this case I would like for it to read "NF".

Is there a way to incorporate this into the Formula?

Try

=IF(ISNA(B2+C2),"NF",IF(AND(A2>=B2,A2<=C2),"F","NF"))

 

[barry houdini]

I have a start and end date, and three Quarters in different columns.

Hello, rogerclue, welcome to MrExcel

If you can put the start date of each quarter in the header cells, e.g. 1-Oct-2009 in O1, 1-Jan-2010 in P1 etc. then you can use this formula in O2 copied across

=MAX(0,MIN($N2,EOMONTH(O$1,2))+1-MAX($M2,O$1))

 

[gfunkb7]

If you have the date to test in A2 and the date range in B2 and C2

=IF(AND(A2>=B2,A2<=C2),"F","NF")

Hi. I was wondering how could I make something like this work BUT with the date range being in another workbook. Also, instead of putting "F" as the true value, it would show the value that's included in the range.

Thanks alot on the matter.

 

[puzzledone]

I'm struggling with a similar thing, I have a date range of work being done, then the data range for the week, if the work range falls into the week I want to get a true result and if not a false result, then conditionally format true.

I'm going round in circles so any help is appreciated.