Background
I've set up a simple "database" in Excel 2003. It's a list of equipment,
recording when it's borrowed, who's got it and when it's due back. I want to
use conditional formatting in the cell containing the date that the
equipment's due for return, based on
(a) the value in that cell (date format) and
(b) the value in another cell (text)

Basically, I want the cell to turn red if the cell saying who's borrowed
the equipment is not blank AND the date in the current cell is earlier than
the current date.

If I just set it to turn it red if the return by date is before today,
then all the blank ones turn red as well (if no-one's borrowed the equipment,
cells in this column are blank). I've tried using "formula is" but I'm not
very good at compound formulae and keep getting error messages. I also tried
add a Yes/No column for "borrowed Y/N" to make the text search simpler (I
don't know how to search for blanks without AutoFilter), but still got error
messages.

Question
Does anyone know how I might do this? It's not a hugely long list, so I
may just leave it at using AutoFilter to pull up non-blanks in the "borrowed
by" or "due back" column and look down through the dates. It would just be
nice to see at a glance if anything's overdue.

Many thanks to anyone who can advise

Assuming

Col A - who' borrowed
ColC - Date.

In C1 applyy conditional formatting to REd with this formula

=IF(A1<"",C1<TODAY())


If this post helps click Yes
---------------
Jacob Skaria


"Amanda Cass" wrote:

Background
I've set up a simple "database" in Excel 2003. It's a list of equipment,
recording when it's borrowed, who's got it and when it's due back. I want to
use conditional formatting in the cell containing the date that the
equipment's due for return, based on
(a) the value in that cell (date format) and
(b) the value in another cell (text)

Basically, I want the cell to turn red if the cell saying who's borrowed
the equipment is not blank AND the date in the current cell is earlier than
the current date.

If I just set it to turn it red if the return by date is before today,
then all the blank ones turn red as well (if no-one's borrowed the equipment,
cells in this column are blank). I've tried using "formula is" but I'm not
very good at compound formulae and keep getting error messages. I also tried
add a Yes/No column for "borrowed Y/N" to make the text search simpler (I
don't know how to search for blanks without AutoFilter), but still got error
messages.

Question
Does anyone know how I might do this? It's not a hugely long list, so I
may just leave it at using AutoFilter to pull up non-blanks in the "borrowed
by" or "due back" column and look down through the dates. It would just be
nice to see at a glance if anything's overdue.

Many thanks to anyone who can advise

Thank you soooooo much, Jacob. This has worked perfectly.

You are a very wonderful person (and I'm a very happy one) :D



"Jacob Skaria" wrote:

Assuming

Col A - who' borrowed
ColC - Date.

In C1 applyy conditional formatting to REd with this formula

=IF(A1<"",C1<TODAY())


If this post helps click Yes
---------------
Jacob Skaria


"Amanda Cass" wrote:

Background
I've set up a simple "database" in Excel 2003. It's a list of equipment,
recording when it's borrowed, who's got it and when it's due back. I want to
use conditional formatting in the cell containing the date that the
equipment's due for return, based on
(a) the value in that cell (date format) and
(b) the value in another cell (text)

Basically, I want the cell to turn red if the cell saying who's borrowed
the equipment is not blank AND the date in the current cell is earlier than
the current date.

If I just set it to turn it red if the return by date is before today,
then all the blank ones turn red as well (if no-one's borrowed the equipment,
cells in this column are blank). I've tried using "formula is" but I'm not
very good at compound formulae and keep getting error messages. I also tried
add a Yes/No column for "borrowed Y/N" to make the text search simpler (I
don't know how to search for blanks without AutoFilter), but still got error
messages.

Question
Does anyone know how I might do this? It's not a hugely long list, so I
may just leave it at using AutoFilter to pull up non-blanks in the "borrowed
by" or "due back" column and look down through the dates. It would just be
nice to see at a glance if anything's overdue.

Many thanks to anyone who can advise

Welcome...

If this post helps click Yes
---------------
Jacob Skaria


"Amanda Cass" wrote:

Thank you soooooo much, Jacob. This has worked perfectly.

You are a very wonderful person (and I'm a very happy one) :D



"Jacob Skaria" wrote:

Assuming

Col A - who' borrowed
ColC - Date.

In C1 applyy conditional formatting to REd with this formula

=IF(A1<"",C1<TODAY())


If this post helps click Yes
---------------
Jacob Skaria


"Amanda Cass" wrote:

Background
I've set up a simple "database" in Excel 2003. It's a list of equipment,
recording when it's borrowed, who's got it and when it's due back. I want to
use conditional formatting in the cell containing the date that the
equipment's due for return, based on
(a) the value in that cell (date format) and
(b) the value in another cell (text)

Basically, I want the cell to turn red if the cell saying who's borrowed
the equipment is not blank AND the date in the current cell is earlier than
the current date.

If I just set it to turn it red if the return by date is before today,
then all the blank ones turn red as well (if no-one's borrowed the equipment,
cells in this column are blank). I've tried using "formula is" but I'm not
very good at compound formulae and keep getting error messages. I also tried
add a Yes/No column for "borrowed Y/N" to make the text search simpler (I
don't know how to search for blanks without AutoFilter), but still got error
messages.

Question
Does anyone know how I might do this? It's not a hugely long list, so I
may just leave it at using AutoFilter to pull up non-blanks in the "borrowed
by" or "due back" column and look down through the dates. It would just be
nice to see at a glance if anything's overdue.

Many thanks to anyone who can advise