Forum Discussion
Solved
Two parameters for conditional formatting
Good afternoon, I am trying to correct some formatting on an excel sheet. I would like the formatting to change the colour of the row when the date is today or older BUT only when one of the column...
- You can use an IF statement to check if the neighboring column contains a date, and then perform the calculation accordingly.
Assuming your "Date off (Day 0)" column is column H and your "Expiry Date" column is column I, you can use the following formula in cell I2:
=IF(ISDATE(H2), H2+5, "")
This formula uses the ISDATE function to check if the cell in column H contains a date. If it does, it adds 5 days to the date in column H (H2+5). If the cell in column H does not contain a date, the formula returns an empty string ("").
You can drag the formula down the column to apply it to the other cells in column I.
The formula will only calculate a result when the corresponding cell in column H is populated with a date.
Otherwise, it will leave the cell in column I blank.
Please adjust the column references and formula as needed based on your specific worksheet layout.
Perfect, that's worked! Thank you.
I was also wondering if it is possible to apply a formula only when a neighbouring column is populated with a date?
For example:
| H | I |
| Date off (Day 0) | Expiry Date |
| "Date to be entered" | "=H+5" |
You can use an IF statement to check if the neighboring column contains a date, and then perform the calculation accordingly.
Assuming your "Date off (Day 0)" column is column H and your "Expiry Date" column is column I, you can use the following formula in cell I2:
=IF(ISDATE(H2), H2+5, "")
This formula uses the ISDATE function to check if the cell in column H contains a date. If it does, it adds 5 days to the date in column H (H2+5). If the cell in column H does not contain a date, the formula returns an empty string ("").
You can drag the formula down the column to apply it to the other cells in column I.
The formula will only calculate a result when the corresponding cell in column H is populated with a date.
Otherwise, it will leave the cell in column I blank.
Please adjust the column references and formula as needed based on your specific worksheet layout.
Assuming your "Date off (Day 0)" column is column H and your "Expiry Date" column is column I, you can use the following formula in cell I2:
=IF(ISDATE(H2), H2+5, "")
This formula uses the ISDATE function to check if the cell in column H contains a date. If it does, it adds 5 days to the date in column H (H2+5). If the cell in column H does not contain a date, the formula returns an empty string ("").
You can drag the formula down the column to apply it to the other cells in column I.
The formula will only calculate a result when the corresponding cell in column H is populated with a date.
Otherwise, it will leave the cell in column I blank.
Please adjust the column references and formula as needed based on your specific worksheet layout.
Thanks again for your help.
When I enter the above it brings back either "invalid" or "NAME?" in the logical test section. Any idea what I've done wrong?
- Please try the following modified formula to check if the neighboring cell in column H contains a date: =IF(ISNUMBER(H2), H2+5, "")
This formula uses the ISNUMBER function instead of ISDATE. In some versions of Excel, ISNUMBER can effectively check if a cell contains a valid date.
If you still encounter errors or unexpected results, please provide more details about the specific version of Excel, Operating system, storage medium, etc. you are using and the format of the data in column H (e.g., whether it contains actual date values or text representations of dates), or a file (without sensitive data) or photo/s with a step-by-step explanation.- That worked, thanks so much for your help 🙂