software

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 quot;date 30 daysquot; 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!

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?

quot;Laura4363quot; wrote:

gt; I've been looking at this all day and I'm sure there's a very simple answer,
gt; maybe a fresh pair of eyes might be able to help me out!
gt; I have a spreadsheet (in Excel 2003) which looks roughly like this:
gt;
gt; A B C
gt; 1 Sent Returned Overdue
gt; 2 10/12/05 04/01/06
gt; 3 20/12/05
gt; 4 12/10/05
gt;
gt; What I want is, if the item hasn't been returned within 30 days of the sent
gt; date, the appropriate cell in the overdue column should turn red. But if it
gt; has been returned, or it's not due yet, the cell should stay blank (i.e.
gt; white). So in the example above, cells C2 and C3 would be blank, and C4
gt; would be red.
gt;
gt; I've tried conditional formatting, but I can't figure out how to get it to
gt; work out the quot;date 30 daysquot; bit in column A. Plus I need it to ignore
gt; column A if there's something in column B. I've found various answers but
gt; they all seem to relate to comparing everything to today's date and that's
gt; not what I want!
gt;
gt; Any help would be appreciated, and I'm sorry if it's a bit confusing!

I Know you dont want to use todays date however this may work
=IF(AND($D$1(or any cell with todays date)-A2lt;30,B2=quot;quot;),quot;Yquot;,quot;Nquot;)
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

quot;Laura4363quot; wrote:

gt; I've been looking at this all day and I'm sure there's a very simple answer,
gt; maybe a fresh pair of eyes might be able to help me out!
gt; I have a spreadsheet (in Excel 2003) which looks roughly like this:
gt;
gt; A B C
gt; 1 Sent Returned Overdue
gt; 2 10/12/05 04/01/06
gt; 3 20/12/05
gt; 4 12/10/05
gt;
gt; What I want is, if the item hasn't been returned within 30 days of the sent
gt; date, the appropriate cell in the overdue column should turn red. But if it
gt; has been returned, or it's not due yet, the cell should stay blank (i.e.
gt; white). So in the example above, cells C2 and C3 would be blank, and C4
gt; would be red.
gt;
gt; I've tried conditional formatting, but I can't figure out how to get it to
gt; work out the quot;date 30 daysquot; bit in column A. Plus I need it to ignore
gt; column A if there's something in column B. I've found various answers but
gt; they all seem to relate to comparing everything to today's date and that's
gt; not what I want!
gt;
gt; Any help would be appreciated, and I'm sorry if it's a bit confusing!

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

=AND(ISBLANK($B19),($C19 30gt;TODAY()))

Then use format painter to replicate it down the column

Regquot;Laura4363quot; wrote:

gt; I've been looking at this all day and I'm sure there's a very simple answer,
gt; maybe a fresh pair of eyes might be able to help me out!
gt; I have a spreadsheet (in Excel 2003) which looks roughly like this:
gt;
gt; A B C
gt; 1 Sent Returned Overdue
gt; 2 10/12/05 04/01/06
gt; 3 20/12/05
gt; 4 12/10/05
gt;
gt; What I want is, if the item hasn't been returned within 30 days of the sent
gt; date, the appropriate cell in the overdue column should turn red. But if it
gt; has been returned, or it's not due yet, the cell should stay blank (i.e.
gt; white). So in the example above, cells C2 and C3 would be blank, and C4
gt; would be red.
gt;
gt; I've tried conditional formatting, but I can't figure out how to get it to
gt; work out the quot;date 30 daysquot; bit in column A. Plus I need it to ignore
gt; column A if there's something in column B. I've found various answers but
gt; they all seem to relate to comparing everything to today's date and that's
gt; not what I want!
gt;
gt; Any help would be appreciated, and I'm sorry if it's a bit confusing!

Sorry that should have read:

=AND(ISBLANK($B19),($A19 30lt;TODAY()))

quot;Laura4363quot; wrote:

gt; I've been looking at this all day and I'm sure there's a very simple answer,
gt; maybe a fresh pair of eyes might be able to help me out!
gt; I have a spreadsheet (in Excel 2003) which looks roughly like this:
gt;
gt; A B C
gt; 1 Sent Returned Overdue
gt; 2 10/12/05 04/01/06
gt; 3 20/12/05
gt; 4 12/10/05
gt;
gt; What I want is, if the item hasn't been returned within 30 days of the sent
gt; date, the appropriate cell in the overdue column should turn red. But if it
gt; has been returned, or it's not due yet, the cell should stay blank (i.e.
gt; white). So in the example above, cells C2 and C3 would be blank, and C4
gt; would be red.
gt;
gt; I've tried conditional formatting, but I can't figure out how to get it to
gt; work out the quot;date 30 daysquot; bit in column A. Plus I need it to ignore
gt; column A if there's something in column B. I've found various answers but
gt; they all seem to relate to comparing everything to today's date and that's
gt; not what I want!
gt;
gt; Any help would be appreciated, and I'm sorry if it's a bit confusing!

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.

quot;Regquot; wrote:

gt; Sorry that should have read:
gt;
gt; =AND(ISBLANK($B19),($A19 30lt;TODAY()))
gt;
gt; quot;Laura4363quot; wrote:
gt;
gt; gt; I've been looking at this all day and I'm sure there's a very simple answer,
gt; gt; maybe a fresh pair of eyes might be able to help me out!
gt; gt; I have a spreadsheet (in Excel 2003) which looks roughly like this:
gt; gt;
gt; gt; A B C
gt; gt; 1 Sent Returned Overdue
gt; gt; 2 10/12/05 04/01/06
gt; gt; 3 20/12/05
gt; gt; 4 12/10/05
gt; gt;
gt; gt; What I want is, if the item hasn't been returned within 30 days of the sent
gt; gt; date, the appropriate cell in the overdue column should turn red. But if it
gt; gt; has been returned, or it's not due yet, the cell should stay blank (i.e.
gt; gt; white). So in the example above, cells C2 and C3 would be blank, and C4
gt; gt; would be red.
gt; gt;
gt; gt; I've tried conditional formatting, but I can't figure out how to get it to
gt; gt; work out the quot;date 30 daysquot; bit in column A. Plus I need it to ignore
gt; gt; column A if there's something in column B. I've found various answers but
gt; gt; they all seem to relate to comparing everything to today's date and that's
gt; gt; not what I want!
gt; gt;
gt; gt; Any help would be appreciated, and I'm sorry if it's a bit confusing!