Excel IF Function

[Thomasd842]

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 

[#2]

@Thomasd842 

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:

Excel

=IF(A2="yes", DATE(YEAR(B2), MONTH(B2), DAY(B2)-10), DATE(YEAR(B2), MONTH(B2), DAY(B2)-5))

Explanation:

• A2: This cell contains the "yes" or "no" value.
• B2: This cell contains the original date.
• 10: This value is subtracted from the date if the cell in A2 contains "yes".
• 5: This value is subtracted from the date if the cell in A2 contains "no".

Breakdown:

1. The IF function checks the value in cell A2.
2. If the value is "yes", it uses the DATE function to:
   • Extract the year (YEAR(B2)) from cell B2.
   • Extract the month (MONTH(B2)) from cell B2.
   • Extract the day (DAY(B2)) from cell B2 and subtracts 10.
3. If the value is "no", it uses the DATE function to:
   • Extract the year (YEAR(B2)) from cell B2.
   • Extract the month (MONTH(B2)) from cell B2.
   • Extract the day (DAY(B2)) from cell B2 and subtracts 5.

Additional Notes:

• You can replace A2 and B2 with the actual cell references in your spreadsheet.
• You can adjust the values 10 and 5 to represent the desired number of days to be subtracted based on the "yes" or "no" condition.
• You can drag this formula down to other rows, and it will automatically adjust the cell references based on the current row.

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.

[#3]

Thanks you very much!
One more thing, if the "no" cell reads "no", what do I do in the formula so the output reads "no" instead of a date?
Thanks again

[#4 · Accepted 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)

[#5]

Excellent, thanks for simplifying

[#6]

@Riny_van_Eekelen 

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

• This formula assumes that the values in cell 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.

[#7]

@SergeiBaklan 

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