Microsoft Tech Community is in Read Only mode.  Please enjoy browsing our content while we complete our platform upgrade.

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.

     

     

     

  • 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.

       

      • Ilgar_Zarbaliyev's avatar
        Ilgar_Zarbaliyev
        Steel Contributor

        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? 

Resources