Hi. I have two columns side by side with dates (due dates and completion
dates). I would like to make the cells in the first column "red" if the date
is < TODAY() (i.e., overdue) and the cell next to it is blank (i.e., not
completed). I would like to make the cells in the first column gray if the
cell next to it is not blank (i.e., completed). I tried to use the following
formula in the Conditional Formatting option to make the cells red and gray
if the formulas were true, but it did not seem to work. The cells turned
gray correctly, but the cells did not turn red if both the conditions below
were present. Can someone please give me some advice?

Condition1: Formula is
=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))<TODAY(),ISB LANK(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1) ))

Condition2: Formula is
=NOT(ISBLANK(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN( ))),0,1)))

Assuming due date is in column B
In B2, condition format condition 1
=AND(ISBLANK(C2),B2<TODAY())
This is your red condition

second condition
=IF(ISBLANK(C2),FALSE,TRUE)
Format grey

Note the lack of "$". Copy this cell, and Right-click, paste special
formatting to all the rest of the cells you want formatted.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"B" wrote:

Hi. I have two columns side by side with dates (due dates and completion
dates). I would like to make the cells in the first column "red" if the date
is < TODAY() (i.e., overdue) and the cell next to it is blank (i.e., not
completed). I would like to make the cells in the first column gray if the
cell next to it is not blank (i.e., completed). I tried to use the following
formula in the Conditional Formatting option to make the cells red and gray
if the formulas were true, but it did not seem to work. The cells turned
gray correctly, but the cells did not turn red if both the conditions below
were present. Can someone please give me some advice?

Condition1: Formula is
=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))<TODAY(),ISB LANK(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1) ))

Condition2: Formula is
=NOT(ISBLANK(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN( ))),0,1)))

Thanks!

"Luke M" wrote:

Assuming due date is in column B
In B2, condition format condition 1
=AND(ISBLANK(C2),B2<TODAY())
This is your red condition

second condition
=IF(ISBLANK(C2),FALSE,TRUE)
Format grey

Note the lack of "$". Copy this cell, and Right-click, paste special
formatting to all the rest of the cells you want formatted.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"B" wrote:

Hi. I have two columns side by side with dates (due dates and completion
dates). I would like to make the cells in the first column "red" if the date
is < TODAY() (i.e., overdue) and the cell next to it is blank (i.e., not
completed). I would like to make the cells in the first column gray if the
cell next to it is not blank (i.e., completed). I tried to use the following
formula in the Conditional Formatting option to make the cells red and gray
if the formulas were true, but it did not seem to work. The cells turned
gray correctly, but the cells did not turn red if both the conditions below
were present. Can someone please give me some advice?

Condition1: Formula is
=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))<TODAY(),ISB LANK(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1) ))

Condition2: Formula is
=NOT(ISBLANK(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN( ))),0,1)))