Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
Hi
I have a spreadsheet that is similar to below: A B C D E F G H I J .... January 1 2 3 4 5 6 7 8 9 10... a a b b b a b b a b February 1 2 3 4 5 6 7 8 9 10... c a c b b a b b a b and so on. So in one column the months are listed, then corresponsingly for two rows there is the date until the end fo the month, and then under each date is basically a task. I know it would probably be easier to change the spreadsheet but it needs to be in this format. I want to grey out dates that have passed, i.e before todays date. I can use =TEXT(TODAY()"m") to create a corresponding reference to the month and grey this out with conditional formatting, and also can obviously grey out numbers in the dates less than todays "d" similarly, the problem is this greys out all dates up to December. Is there a way I can uses AND or something to just grey out numbers relating to the months that have passed? Cheers Rich |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
Use a formula of
=DATEVALUE(B2&"-"&$A2&"-"&YEAR(TODAY())) in CF -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Richhall" wrote in message oups.com... Hi I have a spreadsheet that is similar to below: A B C D E F G H I J .... January 1 2 3 4 5 6 7 8 9 10... a a b b b a b b a b February 1 2 3 4 5 6 7 8 9 10... c a c b b a b b a b and so on. So in one column the months are listed, then corresponsingly for two rows there is the date until the end fo the month, and then under each date is basically a task. I know it would probably be easier to change the spreadsheet but it needs to be in this format. I want to grey out dates that have passed, i.e before todays date. I can use =TEXT(TODAY()"m") to create a corresponding reference to the month and grey this out with conditional formatting, and also can obviously grey out numbers in the dates less than todays "d" similarly, the problem is this greys out all dates up to December. Is there a way I can uses AND or something to just grey out numbers relating to the months that have passed? Cheers Rich |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
On 23 Mar, 12:20, "Bob Phillips" wrote:
Use a formula of =DATEVALUE(B2&"-"&$A2&"-"&YEAR(TODAY())) in CF -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Richhall" wrote in message oups.com... Hi I have a spreadsheet that is similar to below: A B C D E F G H I J .... January 1 2 3 4 5 6 7 8 9 10... a a b b b a b b a b February 1 2 3 4 5 6 7 8 9 10... c a c b b a b b a b and so on. So in one column the months are listed, then corresponsingly for two rows there is the date until the end fo the month, and then under each date is basically a task. I know it would probably be easier to change the spreadsheet but it needs to be in this format. I want to grey out dates that have passed, i.e before todays date. I can use =TEXT(TODAY()"m") to create a corresponding reference to the month and grey this out with conditional formatting, and also can obviously grey out numbers in the dates less than todays "d" similarly, the problem is this greys out all dates up to December. Is there a way I can uses AND or something to just grey out numbers relating to the months that have passed? Cheers Rich- Hide quoted text - - Show quoted text - Cheers I shall give that a go. Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - dates | Excel Discussion (Misc queries) | |||
conditional formatting with dates | Excel Worksheet Functions | |||
Help with Conditional Formatting (Dates) | Excel Discussion (Misc queries) | |||
Conditional Formatting (Dates) | Excel Discussion (Misc queries) | |||
Help with Conditional formatting with Dates | Excel Discussion (Misc queries) |