Forum Discussion
Formula with Date Ranges
Try this one:
=IF(OR(AND(A1>=DATEVALUE("10/19/2020"), A1<=DATEVALUE("10/25/2020")), AND(A1>=DATEVALUE("11/01/2020"), A1<=DATEVALUE("11/06/2020"))),"x","")- You may extend the formula for more dates.
- Adjust dates & cell references in the formula as needed.
Try this one:
=IF(OR(AND(A1>=DATEVALUE("10/19/2020"), A1<=DATEVALUE("10/25/2020")), AND(A1>=DATEVALUE("11/01/2020"), A1<=DATEVALUE("11/06/2020"))),"x","")
- You may extend the formula for more dates.
- Adjust dates & cell references in the formula as needed.
Rajesh_Sinha Thank you this answer did work.
Now I need to figure out how the formula for when multiple different date ranges produce different responses.
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.
Can you help me with this?
- Glad to help you,,, keep asking ☺
You may use this:
=IF(AND(A1>=DATE(2020,10,19),A1<=DATE(2020,10,25)),11,IF(AND(A1>=DATE(2020,10,26),A1<=DATE(2020,11,1)),18,IF(AND(A1>=DATE(2020,11,2),A1<=DATE(2020,11,8)),25,0)))- Adjust dates & cell references in the formula as needed.
taylorcobaugh Oh, you change the rules a bit now, but that can be dealt with. See attached (in the green shaded area).
Thank you for sending that over. I think that the situation I have is a bit different.
I attached my document, it is the first tab titled "WE 8-17".
There is one spot where a user can enter the date, and if that date falls within certain date ranges, I want a certain number to appear.
So every time someone types in a date to a new week, the dates in Row 10 change to next week's dates.
I hope this makes sense.
