Formula with Multiple Date Ranges

Copper Contributor

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.



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


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:


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.



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)



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.






That could be


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


You are right this works!

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


Thank you!

Here you are!



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



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.





Thank you sir for the solution =)

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! 


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

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




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", "??")))

I would help you. I see @Hans Vogelaar already helped:)