Forum Discussion
Excel IF Function
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...
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)
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:
- The IF function checks the value in cell A2.
- 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.
- 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.
