Conditional Formatting dates

  • Thread starter Thread starter AlanN
  • Start date Start date
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
 
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)
 
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
 
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
 
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...
 
Back
Top