Formula with Multiple Date Ranges

[taylorcobaugh]

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.

[#2]

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

[#3]

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

[#4 · Accepted solution]

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

[#5]

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

[#6]

@Ilgar_Zarbaliyev 

You are right this works!

Trying it with DATEVALUE doesn't work but it works with DATE.

Thank you!

[#7]

Here you are!

[#8]

@Ilgar_Zarbaliyev 

Hai Sir kindly help me on this excel range formula:

If date in column A is in between date in 2/3/2022 - 3/8/2022 answer in column B will be Chair

If date in column A is in between date in 4/8/2022 - 9/12/2022 answer in column B will be Table

If date in column A is in between date in 10/12/2022 - 4/4/2023 answer in column B will be Glue

I hope sir can help me on create the formula. Thanks

[#9]

@Atira_Syafiqah_7393 

Please find solution below:

=IF(AND(A12>=DATE(2022, 3,2),A12<=DATE(2022, 8,3)), "Chair",IF(AND(A12>=DATE(2022, 8, 4), A12<=DATE(2022, 12, 9)),"Table",IF(AND(A12>=DATE(2022, 12, 10), A12<=DATE(2023, 4,4)), "Glue", "")))

Sample file is attached to this message.

I hope it will help you.

[#10]

@Ilgar_Zarbaliyev 

Thank you sir for the solution 😃

[#11]

Hi Sir @Ilgar_Zarbaliyev , can you help me, please? I just need the formula to identify the applicable fees (Column D) and cross-check whether the charged fees vs the applicable fee (based on the "Fees" tab) is correct, but there are multiple dates to consider. Can you help me, please?

Thanks a lot!

https://docs.google.com/spreadsheets/d/1V1j6D06caZXHaVZOoRSPBsLmp6hglE3x/edit?usp=sharing&ouid=10608... 

[#12]

@ZanneVA 

The layout of the Fees sheet is not handy, I changed it in the attached version.

[#13]

@HansVogelaar Wow, thanks a lot! I already updated the rest of my file. Thanks again for the help. 

[#14]

@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? 

[#15]

@amgomez512 

=IF(OR(L2={"", "TBD"}, "", IF(L2>=DATE(2024, 1, 5), "OH", IF(L2>=DATE(2023, 11, 9), "NY", "??")))

[#16]

I would help you. I see @HansVogelaar already helped:)