SOLVED

# Formula with Multiple Date Ranges

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

15 Replies

# Re: Formula with Multiple Date Ranges

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.

Good luck.

# Re: Formula with Multiple Date Ranges

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.

best response confirmed by taylorcobaugh (Copper Contributor)
Solution

# Re: Formula with Multiple Date Ranges

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.

# Re: Formula with Multiple Date Ranges

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

# Re: Formula with Multiple Date Ranges

You are right this works!

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

Thank you!

Here you are!

# Re: Formula with Multiple Date Ranges

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

# Re: Formula with Multiple Date Ranges

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

# Re: Formula with Multiple Date Ranges

Thank you sir for the solution =)

# Re: Formula with Multiple Date Ranges

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!

# Re: Formula with Multiple Date Ranges

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

# Re: Formula with Multiple Date Ranges

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

# Re: Formula with Multiple Date Ranges

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?

# Re: Formula with Multiple Date Ranges

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