Feb 27 2024 12:49 AM
Hi,
I have one cell which returns a 'yes' or 'no' based on another cell.
Now I want seperate cell to return a date minus a figure, referencing another dated if the previous cell has 'yes', or minus a different figure if the previous cell has 'no'.
I want to then drag this down so all the following rows conform to this based on that rows informations ('yes' or 'no', and input date).
Thanks
Feb 27 2024 01:11 AM
You can achieve this scenario in Excel using a combination of the IF function and the DATE function. Here's the formula you can use:
=IF(A2="yes", DATE(YEAR(B2), MONTH(B2), DAY(B2)-10), DATE(YEAR(B2), MONTH(B2), DAY(B2)-5))
Explanation:
Breakdown:
Additional Notes:
This formula provides a dynamic way to calculate the adjusted date based on the "yes" or "no" value and the original date in different rows.
Feb 27 2024 02:19 AM
Feb 27 2024 02:44 AM - edited Feb 27 2024 02:44 AM
Solution@Thomasd842 Not sure why the AI robot used to compose the answer you marked as "best" came up with such a complicated DATE construction.
=IF(A2="yes", B2-10, B2-5) works just as good.
To answer your last question, try this:
=IF(A2="yes", B2-10, A2)
Feb 27 2024 07:35 AM - edited Feb 27 2024 07:36 AM
I tried Gemini from Google. On any question it gives 3 variants of answer
First one
=IF(A2="yes", DATEDIF(B2, TODAY(), "D")-5, DATEDIF(B2, TODAY(), "D")-3)
Second one is more friendly but with notice
A2
are exactly "yes" and "no" (case-sensitive). You can adjust the formula using the LOWER
function to make it case-insensitive, like this:
=IF(LOWER(A2)="yes",B2-5,B2-3)
And third, not default variant is
=IF(A2="yes", B2-5, B2-3)
When I asked why LOWER() is needed, 3 reasons for that
1. Using LOWER()
explicitly demonstrates your intention to perform a case-insensitive comparison.
2. While Excel is currently case-insensitive, there's always a chance that future versions might offer case-sensitive options. Using LOWER()
ensures your formulas will continue to function correctly regardless of any future changes
3. Getting into the habit of using LOWER()
for text comparisons can be beneficial, making your code more consistent and transferable across different situations.
People need deep knowledge of Excel to use AI answers efficiency. Or to make the decision not to use the answer since it could be simply wrong.
Feb 27 2024 11:06 AM
"People need deep knowledge of Excel to use AI answers"
Agreed! Though, unfortunately, most who post these AI answers here don't know what they are talking about! And they don't understand they do more harm than good.
Feb 27 2024 02:44 AM - edited Feb 27 2024 02:44 AM
Solution@Thomasd842 Not sure why the AI robot used to compose the answer you marked as "best" came up with such a complicated DATE construction.
=IF(A2="yes", B2-10, B2-5) works just as good.
To answer your last question, try this:
=IF(A2="yes", B2-10, A2)