Forum Discussion
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...
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.
taylorcobaugh As a variant, have a look at the attached workbook. Include the star and end dates in two named ranges (start and end) and use a formula like:
=IF(SUM((A10>=start)*(A10<=end)),"X","")where A10 holds the date you want to test. No need for nested IFS and particularly handy when you need to add more than just a few date ranges. Obviously, your dates have to be real dates, not texts looking like dates.
Your formula needs little correction,,, and it must be an array (CSE) formula, need to finish with Ctrl+Shift+Enter,, otherwise you get #VALUE error !!