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.
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 08, 2020

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.

Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Oct 08, 2020
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 !!
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 08, 2020

    Rajesh_Sinha All depends on your Excel version. I'm on the most recent one and CSE is never an issue. It is my understanding that Excel silently converts such formulae to CSE style in older versions. But perhaps I'm mistaken. Then, indeed, such formulae should be entered with CSE.

    • Rajesh_Sinha's avatar
      Rajesh_Sinha
      Iron Contributor
      Oct 08, 2020

      Riny_van_Eekelen ,,,

       

      If you are talking about this =ArrayFormula(SUM(A1:A5 * B1:B5)) then, this happens in Google Sheet only,,, where user has to use either "ArrayFormula" the reserve word or even executes CSE ,, Google Sheet adds ArrayFormula with the formula!!

       

      With Excel 2016, 2019 & 365 Dynamic array formula has been introduced but list is very small and doesn't covers the formula U have used.

       

      Now check the File you have uploaded ,,,, I've examined and returning to you ,,, no where I found EXCEL automatically applied  CSE ,,, Ctrl+Shift+Enter !!  And I've opened it with 2013.

Resources