Count date
 

Hi, I would like to use the conditional formatting. I can use it for numeric,
but how to count date for 2 column ?

If I want column B can not be 7 days more than column A. If not, Column B
will show a different colour. How can it be done ?

Hardware deliver date Deployment Date
1 1/11/2007 2/11/2007
2 1/11/2007 1/19/2007
3 12/11/2007 12/15/2007
4 12/12/2007 12/15/2007
5 12/13/2007 12/20/2007
6 12/14/2007 12/20/2007
7 12/15/2007 12/20/2007

Suppose 2 b & 5 b will show a different colour. Many thanks

Count date
 

"Jason Au Yeung" wrote:

Hi, I would like to use the conditional formatting. I can use it for numeric,
but how to count date for 2 column ?

If I want column B can not be 7 days more than column A. If not, Column B
will show a different colour. How can it be done ?

Hardware deliver date Deployment Date
1 1/11/2007 2/11/2007
2 1/11/2007 1/19/2007
3 12/11/2007 12/15/2007
4 12/12/2007 12/15/2007
5 12/13/2007 12/20/2007
6 12/14/2007 12/20/2007
7 12/15/2007 12/20/2007

Suppose 2 b & 5 b will show a different colour. Many thanks

Count date
 

because excel stores data as integer you can subtract them:
formula is =B1-A17
and then you set the background to red or whatever you want.
Then you can copy this format down.

hth

Carlo

On Nov 22, 10:43 am, Jason Au Yeung
wrote:
Hi, I would like to use the conditional formatting. I can use it for numeric,
but how to count date for 2 column ?

If I want column B can not be 7 days more than column A. If not, Column B
will show a different colour. How can it be done ?

Hardware deliver date Deployment Date
1 1/11/2007 2/11/2007
2 1/11/2007 1/19/2007
3 12/11/2007 12/15/2007
4 12/12/2007 12/15/2007
5 12/13/2007 12/20/2007
6 12/14/2007 12/20/2007
7 12/15/2007 12/20/2007

Suppose 2 b & 5 b will show a different colour. Many thanks

Count date
 

Oops...sorry for the blank email

Try something like this:

cell value is: greater then =$A1+7

or you can use: Formula is: =$B1$A1+7

"pinmaster" wrote:



"Jason Au Yeung" wrote:

Hi, I would like to use the conditional formatting. I can use it for numeric,
but how to count date for 2 column ?

If I want column B can not be 7 days more than column A. If not, Column B
will show a different colour. How can it be done ?

Hardware deliver date Deployment Date
1 1/11/2007 2/11/2007
2 1/11/2007 1/19/2007
3 12/11/2007 12/15/2007
4 12/12/2007 12/15/2007
5 12/13/2007 12/20/2007
6 12/14/2007 12/20/2007
7 12/15/2007 12/20/2007

Suppose 2 b & 5 b will show a different colour. Many thanks

Count date
 

On Wed, 21 Nov 2007 17:43:02 -0800, Jason Au Yeung
wrote:

Hi, I would like to use the conditional formatting. I can use it for numeric,
but how to count date for 2 column ?

If I want column B can not be 7 days more than column A. If not, Column B
will show a different colour. How can it be done ?

Hardware deliver date Deployment Date
1 1/11/2007 2/11/2007
2 1/11/2007 1/19/2007
3 12/11/2007 12/15/2007
4 12/12/2007 12/15/2007
5 12/13/2007 12/20/2007
6 12/14/2007 12/20/2007
7 12/15/2007 12/20/2007

Suppose 2 b & 5 b will show a different colour. Many thanks



B2 conditional format:

Cell Value Is greater than or equal to =$A2+7


--ron

Count date
 

Select A2:B8

CFFormula is: =$B2$A2+6

Note the $ sign to lock the column but not the row.

Format to a color and OK your way out.

For me it colors B1, B2 and B6

Your row numbers appear to out by one in your example.

If you mean column B cannot be more than 7 days greater than column A, change
the $A2+6 to =$A2 which will then also color B7




On Wed, 21 Nov 2007 17:43:02 -0800, Jason Au Yeung
wrote:

Hi, I would like to use the conditional formatting. I can use it for numeric,
but how to count date for 2 column ?

If I want column B can not be 7 days more than column A. If not, Column B
will show a different colour. How can it be done ?

Hardware deliver date Deployment Date
1 1/11/2007 2/11/2007
2 1/11/2007 1/19/2007
3 12/11/2007 12/15/2007
4 12/12/2007 12/15/2007
5 12/13/2007 12/20/2007
6 12/14/2007 12/20/2007
7 12/15/2007 12/20/2007

Suppose 2 b & 5 b will show a different colour. Many thanks