Formula with Multiple Date Ranges

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

6 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 (Occasional 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!