Forum Discussion

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

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 - ...
excel
Formulas and Functions
  • Ilgar_Zarbaliyev's avatar
    Ilgar_Zarbaliyev
    Oct 08, 2020

    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.

     

     

     

Ilgar_Zarbaliyev's avatar
Ilgar_Zarbaliyev
Steel Contributor
Oct 08, 2020

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
    Oct 08, 2020

    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
      Oct 08, 2020

      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,...)

    • Ilgar_Zarbaliyev's avatar
      Ilgar_Zarbaliyev
      Steel Contributor
      Oct 08, 2020

      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.

       

       

       

Resources