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