Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
count the mean in few date but on de date it has various data | Excel Worksheet Functions | |||
count between start date and end date | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |