Forum Discussion
taylorcobaugh
Oct 08, 2020Copper Contributor
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.
- Ilgar_ZarbaliyevSteel Contributor
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.
- taylorcobaughCopper Contributor
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_ZarbaliyevSteel Contributor
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.
- amgomez512Copper Contributor
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?
=IF(OR(L2={"", "TBD"}, "", IF(L2>=DATE(2024, 1, 5), "OH", IF(L2>=DATE(2023, 11, 9), "NY", "??")))
- Ilgar_ZarbaliyevSteel ContributorI would help you. I see HansVogelaar already helped:)