Conditional Formatting: Blank, >Today, <Today

[Hobbes2099]

This is my first post, so please bare with me;

I'm starting out at a new job and alot of my work involves filling out reports and developing graphs and table with this information. I've seen that most of the tables and graphs are done by hand, and now that i've inherited the responsibility, i really don't like manually typing and formating about 100 records...

im trying to automatically format a table where there could be three options:

Cell is Blank (no format)
Cell's value is before present month (in this case Decemeber)
Cell's value is the present month (in this case December),

that way, next month i don't need to change all december cell's format, it will be done automatically, and all cells i fill in during January will have new format, making it easier for fast and quick read.

_____Column A Column B Column C

Row 1 Apr-04 May-04 Dec-05

Row 2 May-05 [Blank] Dec-05

Row 3 [Blank] Nov-05 Dec-05

in the example above, all of Column C would have a diff. format than the rest of the table, [Blanks would remain without format, and on January 1st, 2006, all the [Blank]cells still remain unformatted, and now, since the Dec cells dont have the present month, are automatically changed to 'generic' format.

the format im looking for is White or non format of Blank
Black Font, Any color for Bg Shading (for example Light Green)
Same Font and Bg color Shading (Light Green on Light Green),

thus making the new months additions stand out, and get a general feeling of how the projects progress.

the problem is, when i try to apply the Conditional formating

thnx!

 

[NBVC]

I am not sure if you by format you mean, changing font or colour or by adding a background colour.

If so, then select entire range

Go to Format|Conditional Format

Select Formula Is from the drop down and enter

=A1<Today()

, then click Format and choose formats to apply. Click Ok,

Then back in the original dialogue box, click Add and select Formula Is again. Now enter =A1>Today() and repeat the formatting.

If you want a separate format for =Today then Add another condition and enter =Today().

You don't need anything for blank as it will remain unformatted.

 

[lenze]

Originally posted by NVBC
You don't need anything for blank as it will remain unformatted.

Actually, you do need to account for blanks, as a blank cell will format as less than today..

I suggest these conditions

Cond 1 =ISBLANK(A1) 'Format as No Color

Cond 2 =MONTH(A1)=MONTH(NOW()) 'Format some color

Cond 3 =MONTH(NOW())>MONTH(A1) 'Format some color

HTH

lenze

 

[NBVC]

Originally posted by NVBC
You don't need anything for blank as it will remain unformatted.

Actually, you do need to account for blanks, as a blank cell will format as less than today..


lenze

Hi Lenze, i tested that with and without formulas in the cell (ie. returning blanks, etc) and the format I applied for less than today(), does not show.

Can you give an example of when a blank will show the format applied with the condition stated?

 

[NBVC]

Hmmm,

Thanks Lenze, that didn't happen when I did that test on my already opened book, where I had tested that formula...

 

[lenze]

Originally Posted By NBVC
Can you give an example of when a blank will show the format applied with the condition stated?

Open a new WorkBook and select a Range of cells, say A1:D7.

Now in conditional formatting, with Formula Is enter

=A1<TODAY()

Select a backgrond color and choose OK.
When you exit CF, the entire range will change to your chosen color.

lenze

EDIT:NBVC, it seems as our last 2 got reversed when I deleted my original post momentarily to use the code feature

 

[Hobbes2099]

Almost but not quite,

indeed, a Blank cell is considered smaller than today, so the second CF applied.

Cond 1 =ISBLANK(A1)

Cond 2 =MONTH(A1)=MONTH(NOW())

Cond 3 =MONTH(NOW())>MONTH(A1)

except that there is a slight bug;

when I put dates (my dates are only month and year, if that helps) such as Decemebr 2003, or December 2004, it applies the special format (and yes, by format i meant font and color changing) also, because the formula checks only to see if the MONTH is the same or earlier, not the complete date.

so my new question is...

how do i include YEAR(NOW()) into the equation? because if i just change Month for Year, all 2005 records will high light

 

[lenze]

Maybe

=AND(MONTH(A1)=MONTH(NOW()),YEAR(A1)=YEAR(NOW()))

lenze

 

[Hobbes2099]

OK, got it!

=ISBLANK(D7)
=AND(MONTH(D7)=MONTH(NOW()),YEAR(D7)=YEAR(NOW()))
=OR(MONTH(D7)<MONTH(NOW()),YEAR(D7)<YEAR(NOW()))

with AND(), with either condition being false, i would never get (or hardly ever) the formatting i want. if its July 2006, and i have a date labeled march 2006, sure, month is smaller but year isnt

and if i put Sept-2005, year is smaller but month is bigger.

So Condition2 must be an AND statement, while Condition 3 must be an OR statement, so that with one of the nested statements being true, ill get the formatting i want,

thnx guys!!!!

 

[lenze]

For future reference, you could also use

=NOT(AND(MONTH(A1)=MONTH(NOW()),YEAR(A1)=YEAR(NOW())))

as the 3rd condition

lenze