Formula

[Dave]

Cant get my head around this, if a task hasn't been ticked for two weeks and
over for the corresponding weeks, I want the Task to flash up in red. So in
the exmple below Task2 would flash up red. It would go off red as soon as
the 1 is placed in a new week, so for task2 if 25 Dec was ticked then the
red would disappear.

December January
4 11 18 25 1 8 15 22 29
Task1 1 1 1 1
Task2 1
Task3 1 1 1
Task4 1 1 1 1

 

[pinmaster]

Hi,

assuming your month day is actually dates formatted as "dd" and not just a
number then select your tasks cells and try this in conditional formatting:

=SUMPRODUCT(($A$3:$A$6=A3)*($B$2:$J2<=TODAY())*($B$2:$J$2>=TODAY()-14),$B$3:$J$6)=0


where A3:A6 are your tasks from 1 to 4, B2:J2 are your month days formatted
as "dd" and B3:J6 are your "ticks" as you put it.

HTH
Jean-Guy

 

[JMB]

First, I would use actual dates for the first row, such as 12/4/06, 12/11/06,
etc, so that the table looks like:

A B C
1 12/4/2006 12/11/2006
2 Task1 1 1


Assume this table is in A1:J5
Then, I would select Task1 through Task4, click Format/Conditional
Formatting, Formula Is, and enter
=TODAY()-LOOKUP(1,2:2,$1:$1)>=14

Then click Format and select Font/Red. Click OK, OK.

Of course, 2:2 and $1:$1 can be restricted if your table is a fixed size (or
if you have other data to the right of your table). So, this particular
example would be
=TODAY()-LOOKUP(1,B2:J2,$B$1:$J$1)>=14

 

[Dave]

Works a treat, thanks alot JMB.

First, I would use actual dates for the first row, such as 12/4/06, 12/11/06,
etc, so that the table looks like:

A B C
1 12/4/2006 12/11/2006
2 Task1 1 1


Assume this table is in A1:J5
Then, I would select Task1 through Task4, click Format/Conditional
Formatting, Formula Is, and enter
=TODAY()-LOOKUP(1,2:2,$1:$1)>=14

Then click Format and select Font/Red. Click OK, OK.

Of course, 2:2 and $1:$1 can be restricted if your table is a fixed size (or
if you have other data to the right of your table). So, this particular
example would be
=TODAY()-LOOKUP(1,B2:J2,$B$1:$J$1)>=14

 

[Dave]

Works a treat thanks alot JMB.

First, I would use actual dates for the first row, such as 12/4/06, 12/11/06,
etc, so that the table looks like:

A B C
1 12/4/2006 12/11/2006
2 Task1 1 1


Assume this table is in A1:J5
Then, I would select Task1 through Task4, click Format/Conditional
Formatting, Formula Is, and enter
=TODAY()-LOOKUP(1,2:2,$1:$1)>=14

Then click Format and select Font/Red. Click OK, OK.

Of course, 2:2 and $1:$1 can be restricted if your table is a fixed size (or
if you have other data to the right of your table). So, this particular
example would be
=TODAY()-LOOKUP(1,B2:J2,$B$1:$J$1)>=14

 

[JMB]

You're welcome.

Works a treat, thanks alot JMB.