Dates

[Guest]

I have a large order spreadsheet listing orders placed by customers. There
are three date columns. Column K is the date the customer would like the
product delivered, column L is the date that is quoted by the system and
column M is the acknowledged delivery date from the supplier.

I would like to set up a system whereby if the date in any of the three
columns is nearby (say by 14 days), then that cell is flagged in yellow to
alert me to chase the delivery, but when the date becomes 7 days away, the
cell colour becomes red to really alert me to chase the delivery.

There is also a column (O) which is the despatch date. What I would like to
happen is for the three columns above to flag with an approaching date, but
as soon as I enter a date in the despatch date column (column O), the other
three fields stop being red.

Can I do this?

 

[Francois via OfficeKB.com]

I have a large order spreadsheet listing orders placed by customers. There
are three date columns. Column K is the date the customer would like the
product delivered, column L is the date that is quoted by the system and
column M is the acknowledged delivery date from the supplier.

I would like to set up a system whereby if the date in any of the three
columns is nearby (say by 14 days), then that cell is flagged in yellow to
alert me to chase the delivery, but when the date becomes 7 days away, the
cell colour becomes red to really alert me to chase the delivery.

There is also a column (O) which is the despatch date. What I would like to
happen is for the three columns above to flag with an approaching date, but
as soon as I enter a date in the despatch date column (column O), the other
three fields stop being red.

Can I do this?

Conditional Format:-

In cell K1 (or whatever) enter Formula is =K1<=NOW()+14 set your
colour scheme
ALSO as condition 2 enter Formula is =K1<=NOW()+7 set different
colour scheme
ALSO as condition 3 enter Formula is =O1 <=NOW() set final
colour scheme

I haven't had time to test it , but you will get the drift (i hope) good
luck

 

[Sandy Mann]

Use Conditional Formattion:

In Column K (I am using cell K3)

Format > Conditional Formatting > Click on arrowhead and select Formula is
and enter:

=AND($O3="",TODAY()-K3<=7)

then select the Red pattern formmatting you want

then select ADD >> for the second condition and select Formula is again and
enter the formula:

=AND($O3="",TODAY()-K3<=14)

and select yellow and then OK

Use the format painter to copy the formtting to the other cells

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Francois via OfficeKB.com]

I have a large order spreadsheet listing orders placed by customers. There
are three date columns. Column K is the date the customer would like the

[quoted text clipped - 12 lines]

Can I do this?

Conditional Format:-

In cell K1 (or whatever) enter Formula is =K1<=NOW()+14 set your
colour scheme
ALSO as condition 2 enter Formula is =K1<=NOW()+7 set different
colour scheme
ALSO as condition 3 enter Formula is =O1 <=NOW() set final
colour scheme

I haven't had time to test it , but you will get the drift (i hope) good
luck

As I posted this I thought that I might have the formulas 1,2,3 reversed

try doing it 3,2,1

Sorry