Oct 08 2020 01:26 PM
Oct 08 2020 01:26 PM
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.
Oct 08 2020 02:03 PM
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.
Oct 08 2020 02:13 PM
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.
Oct 08 2020 02:41 PM
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,...)
Apr 17 2023 12:06 AM
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
Apr 17 2023 05:33 AM
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.
May 29 2023 11:11 AM
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!