Conditional formatting based on a date + 30 days
 

I've been looking at this all day and I'm sure there's a very simple answer,
maybe a fresh pair of eyes might be able to help me out!
I have a spreadsheet (in Excel 2003) which looks roughly like this:

A B C
1 Sent Returned Overdue
2 10/12/05 04/01/06
3 20/12/05
4 12/10/05

What I want is, if the item hasn't been returned within 30 days of the sent
date, the appropriate cell in the overdue column should turn red. But if it
has been returned, or it's not due yet, the cell should stay blank (i.e.
white). So in the example above, cells C2 and C3 would be blank, and C4
would be red.

I've tried conditional formatting, but I can't figure out how to get it to
work out the "date + 30 days" bit in column A. Plus I need it to ignore
column A if there's something in column B. I've found various answers but
they all seem to relate to comparing everything to today's date and that's
not what I want!

Any help would be appreciated, and I'm sorry if it's a bit confusing!

Conditional formatting based on a date + 30 days
 

Hi Laura,

You need to enter a formula into C2 that subtracts B2 from A2 (=B2-A2).
Make sure to format C2 as a number. Then, in the conditional formatting,
have it color the cell red if the number is greater than 30. You can format
the text white so that the number doesn't show if you want.

Does this help?

"Laura4363" wrote:

I've been looking at this all day and I'm sure there's a very simple answer,
maybe a fresh pair of eyes might be able to help me out!
I have a spreadsheet (in Excel 2003) which looks roughly like this:

A B C
1 Sent Returned Overdue
2 10/12/05 04/01/06
3 20/12/05
4 12/10/05

What I want is, if the item hasn't been returned within 30 days of the sent
date, the appropriate cell in the overdue column should turn red. But if it
has been returned, or it's not due yet, the cell should stay blank (i.e.
white). So in the example above, cells C2 and C3 would be blank, and C4
would be red.

I've tried conditional formatting, but I can't figure out how to get it to
work out the "date + 30 days" bit in column A. Plus I need it to ignore
column A if there's something in column B. I've found various answers but
they all seem to relate to comparing everything to today's date and that's
not what I want!

Any help would be appreciated, and I'm sorry if it's a bit confusing!

Conditional formatting based on a date + 30 days
 

I Know you dont want to use todays date however this may work
=IF(AND($D$1(or any cell with todays date)-A2<30,B2=""),"Y","N")
To get todays date just use an =TODAY()
Then you would just have to conditionally format the cells as to whether
they have a Y or N in them

"Laura4363" wrote:

I've been looking at this all day and I'm sure there's a very simple answer,
maybe a fresh pair of eyes might be able to help me out!
I have a spreadsheet (in Excel 2003) which looks roughly like this:

A B C
1 Sent Returned Overdue
2 10/12/05 04/01/06
3 20/12/05
4 12/10/05

What I want is, if the item hasn't been returned within 30 days of the sent
date, the appropriate cell in the overdue column should turn red. But if it
has been returned, or it's not due yet, the cell should stay blank (i.e.
white). So in the example above, cells C2 and C3 would be blank, and C4
would be red.

I've tried conditional formatting, but I can't figure out how to get it to
work out the "date + 30 days" bit in column A. Plus I need it to ignore
column A if there's something in column B. I've found various answers but
they all seem to relate to comparing everything to today's date and that's
not what I want!

Any help would be appreciated, and I'm sorry if it's a bit confusing!

Conditional formatting based on a date + 30 days
 

in the conditional format c2 use 'formula is' and put

=AND(ISBLANK($B19),($C19+30TODAY()))

Then use format painter to replicate it down the column

Reg


"Laura4363" wrote:

I've been looking at this all day and I'm sure there's a very simple answer,
maybe a fresh pair of eyes might be able to help me out!
I have a spreadsheet (in Excel 2003) which looks roughly like this:

A B C
1 Sent Returned Overdue
2 10/12/05 04/01/06
3 20/12/05
4 12/10/05

What I want is, if the item hasn't been returned within 30 days of the sent
date, the appropriate cell in the overdue column should turn red. But if it
has been returned, or it's not due yet, the cell should stay blank (i.e.
white). So in the example above, cells C2 and C3 would be blank, and C4
would be red.

I've tried conditional formatting, but I can't figure out how to get it to
work out the "date + 30 days" bit in column A. Plus I need it to ignore
column A if there's something in column B. I've found various answers but
they all seem to relate to comparing everything to today's date and that's
not what I want!

Any help would be appreciated, and I'm sorry if it's a bit confusing!

Conditional formatting based on a date + 30 days
 

Sorry that should have read:

=AND(ISBLANK($B19),($A19+30<TODAY()))

"Laura4363" wrote:

I've been looking at this all day and I'm sure there's a very simple answer,
maybe a fresh pair of eyes might be able to help me out!
I have a spreadsheet (in Excel 2003) which looks roughly like this:

A B C
1 Sent Returned Overdue
2 10/12/05 04/01/06
3 20/12/05
4 12/10/05

What I want is, if the item hasn't been returned within 30 days of the sent
date, the appropriate cell in the overdue column should turn red. But if it
has been returned, or it's not due yet, the cell should stay blank (i.e.
white). So in the example above, cells C2 and C3 would be blank, and C4
would be red.

I've tried conditional formatting, but I can't figure out how to get it to
work out the "date + 30 days" bit in column A. Plus I need it to ignore
column A if there's something in column B. I've found various answers but
they all seem to relate to comparing everything to today's date and that's
not what I want!

Any help would be appreciated, and I'm sorry if it's a bit confusing!

Conditional formatting based on a date + 30 days
 

Sorry, I wasn't ignoring everybody, I thought I would get a mail when there
was a reply but I didn't! Thanks for all the answers, but none of them seem
to work.

"Reg" wrote:

Sorry that should have read:

=AND(ISBLANK($B19),($A19+30<TODAY()))

"Laura4363" wrote:

I've been looking at this all day and I'm sure there's a very simple answer,
maybe a fresh pair of eyes might be able to help me out!
I have a spreadsheet (in Excel 2003) which looks roughly like this:

A B C
1 Sent Returned Overdue
2 10/12/05 04/01/06
3 20/12/05
4 12/10/05

What I want is, if the item hasn't been returned within 30 days of the sent
date, the appropriate cell in the overdue column should turn red. But if it
has been returned, or it's not due yet, the cell should stay blank (i.e.
white). So in the example above, cells C2 and C3 would be blank, and C4
would be red.

I've tried conditional formatting, but I can't figure out how to get it to
work out the "date + 30 days" bit in column A. Plus I need it to ignore
column A if there's something in column B. I've found various answers but
they all seem to relate to comparing everything to today's date and that's
not what I want!

Any help would be appreciated, and I'm sorry if it's a bit confusing!