Forum Discussion

taylorcobaugh's avatar
taylorcobaugh
Copper Contributor
Oct 07, 2020
Solved

Formula with Date Ranges

I would like for an "X" to appear in a cell when certain date ranges are entered in another cell.   I currently have a formula for the X to appear when the date range 10/19/2020 - 10/25/2020 is ent...
excel
Formulas and Functions
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Oct 08, 2020

    taylorcobaugh 

     

    Try this one:

     

    =IF(OR(AND(A1>=DATEVALUE("10/19/2020"), A1<=DATEVALUE("10/25/2020")), AND(A1>=DATEVALUE("11/01/2020"), A1<=DATEVALUE("11/06/2020"))),"x","")

     

    • You may extend the formula for more dates.
    • Adjust dates & cell references in the formula as needed.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 08, 2020

taylorcobaugh 

Someone might well have suggested a table to contain the date ranges (I haven't opened all the files)

 
 

If you wish to return IDs for the ranges then the formula extends to

 

 

= IFERROR( 
  IF(LOOKUP(date, startRange, endRange) >= date, 
     LOOKUP(date, startRange, rangeID), 
     "-" ), 
  "-")

 

 

replacing the "X" by a further lookup.

 

Resources