Forum Discussion

taylorcobaugh's avatar
taylorcobaugh
Copper Contributor
Oct 08, 2020

Formula with Multiple Date Ranges

I need to figure out a formula for when a date within a certain range is entered in one cell, a certain character is produced in another cell.

 

Example:

I want for the date range of 10/19/2020 - 10/25/2020 to produce 11 in one cell.

And when the date range is 10/26/2020 - 11/01/2020, I want it to produce 18 in the cell.

Then when the date range is 11/02/2020 - 11/08/2020, I want it to produce 25 in the cell.

 

And so on. I have many date ranges that need to produce new numbers.

  • taylorcobaugh 

     

    Hi there,

    Actually, my formula is also for one cell result oriented. Just , it has been written in three rows in my previous response message box.

     

     

     

  • amgomez512's avatar
    amgomez512
    Copper Contributor

    Ilgar_Zarbaliyev 

    Hello, 

     

    Hoping you can help me. 

     

    If date in cell L2 is in between date in 11/9/2023 - 1/4/2024 answer in cell M2 will be NY

    If date in cell L2 is greater than 1/5/2024 answer in cell M2 will be OH

    If date in cell L2 TBD or Blank answer in cell M2 will be blank

     

    Would you be able to help create a formula for this? 

  • taylorcobaugh 

    Hi there,

    I hope the following formula will help you to solve this issue:

     

    =IF(AND(A2>=DATE(2020,10,19), B2<=DATE(2020,10,25)), 11, IF(AND(A2>=DATE(2020,10,26), B2<=DATE(2020,11,1)), 18, IF(AND(A2>=DATE(2020,11,2), B2<=DATE(2020,11,8)),25,"")))

    Cell A - beginning date of range where Cell B shows ending date of date range.

     

    Please note attached file for your further consideration.

     

    If you consider this response your best one, please note.

    Good luck.

     

     

    • taylorcobaugh's avatar
      taylorcobaugh
      Copper Contributor

      Ilgar_Zarbaliyev 

      Hi Edgar, thank you for your response. 

      I am actually trying to get a formula for one cell only. 

       

      The formula I currently have for the cell is:

      =IF(AND($BK$4>=DATEVALUE("10/19/2020"),$BK$4<=DATEVALUE("10/25/2020")),"11","")

      This formula populates "11" when someone enters a date between the date range 10/19 - 10/25.

       

      Now I need to make it so that the cell will also populate a different number, when a date in a different range is entered.

       

      Example:

      A date entered between 10/19/20 - 10/25/20 will populate 11

      A date entered between 10/26/20 - 11/01/20 will populate 18

      A date entered between 11/02/20 - 11/08/20 will populate 26

       

      I am needing code for all of this for the same cell.

       

      I have attached my document with the 2 cells I am working with highlighted in Green.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        taylorcobaugh 

        That could be

        =IFERROR(INDEX({11,18,26},INT(($BK$4-DATE(2020,10,19))/7)+1),"")

        In general that's not a good idea to hardcode dates within all formulas. Better to have only one starting date or month/year and make all calculations based on it. Includes weekdays names (M,T,W,...)

Resources