Conditional Formatting dates

A

AlanN

In column A are dates starting at 01/01/2004 and going to 12/31/2004 by day.
I want to use conditional formatting to highlight paydates. The best logic I
can 'write' is ... starting (and including) on Jan 2nd, 2004 and every 14
days thereafter, highlight the cell blue.

Can anyone give me a solution to do that?

TIA, AlanN
 
B

Bob Phillips

Alan,

Use a CF condition of Formula Is, with a formula of

=MOD(A1,14)=6

then format as required

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

JMay

With A1 Cell Active, Go to Format CF, use "FormulaIs" and in box enter:
=IF(MOD(A1-37988,14)=0,TRUE,FALSE) ' the number 37988 is 1/02/2004
Select your pattern, etc and OK out...
Then with A1 Active Select the FormatPaint Icon and Paint A2 and downward.
HTH
JMay
 
J

JE McGimpsey

Note that hardcoding the 6 causes the CF to fail if the 1904 date system
is used. This could be corrected by using something like:

CF1: =MOD(A1,14)=MOD("1/2/2004",14)

or

CF1: =MOD(A1-DATE(2004,1,2),14)=0
 
A

AlanN

Thanks for this- works great!

Alan
JE McGimpsey said:
Note that hardcoding the 6 causes the CF to fail if the 1904 date system
is used. This could be corrected by using something like:

CF1: =MOD(A1,14)=MOD("1/2/2004",14)

or

CF1: =MOD(A1-DATE(2004,1,2),14)=0
Click to expand...
 
Top