Forum Discussion
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.
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.
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?
- I would help you. I see HansVogelaar already helped:)
=IF(OR(L2={"", "TBD"}, "", IF(L2>=DATE(2024, 1, 5), "OH", IF(L2>=DATE(2023, 11, 9), "NY", "??")))
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.
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,...)
