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 columns is empty (ie not released). I have managed so far to get the singular column to change once displaying todays date or older. However, I cannot figure out how to apply this to the row and only apply when the separate release column is empty. I suspect I need an "AND" formula?
A simplified table to illustrate what I'm working with:
| Column letter: | F | L |
| 5 Day Warning | Release Date | |
| 22-May-23 | 21-May-23 | |
| 23-May-23 | ||
| 24-May-23 | ||
| 25-May-23 |
Current formula: =$F3<=TODAY()
Many thanks for any help 🙂
- 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.
