Forum Discussion
Two parameters for conditional formatting
- 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.
Enter the following formula in the formula field:
=AND($F1<=TODAY(), $L1="")
This formula checks if the date in column F is today or older ($F1<=TODAY()) and if the release column L is empty ($L1=""). Note that the row number (1) should match the starting row of your selected range.
The conditional formatting will now change the color of the rows where the date in column F is today or older, but only if the corresponding release column L is empty. Adjust the formula and formatting options as needed for your specific requirements.
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.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?
