Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
singingsister said:
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?
Click to expand...


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
 
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 said:
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
Click to expand...
[quoted text clipped - 12 lines]
Can I do this?
Click to expand...

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
Click to expand...


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

try doing it 3,2,1

Sorry
 
Back
Top