Forum Discussion
taylorcobaugh
Oct 08, 2020Copper 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 - ...
- Oct 08, 2020
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.
Ilgar_Zarbaliyev
Oct 08, 2020Steel Contributor
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.
taylorcobaugh
Oct 08, 2020Copper Contributor
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.
- SergeiBaklanOct 08, 2020MVP
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,...)
- Ilgar_ZarbaliyevOct 08, 2020Steel 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.
- ZanneVAMay 29, 2023Copper Contributor
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=106089949912200570919&rtpof=true&sd=true- HansVogelaarMay 29, 2023MVP
The layout of the Fees sheet is not handy, I changed it in the attached version.
- taylorcobaughOct 08, 2020Copper Contributor
You are right this works!
Trying it with DATEVALUE doesn't work but it works with DATE.
Thank you!
- Ilgar_ZarbaliyevOct 08, 2020Steel ContributorHere you are!