January 2006

H

Howard

In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that
January 2006 has rolled around, I want the column with the date 12/31/2005 to
highlight using conditional formatting.
The Month function works well within the current year
(=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month
(Dec) to highlight when the first month of the current year rolls around?
 
P

pinmaster

Similar as what you have now except with Year() added

=AND(MONTH(A$1)=MONTH(TODAY())-1,YEAR(A$1)=YEAR(TODAY())-1)

Regards
J
 
P

pinmaster

Try this:

=AND(MONTH(A1)=12,MONTH(TODAY())=1,YEAR(TODAY())=YEAR(A1)+1)

HTH
JG
 
J

JAdamJ

if you are sure that your dates are the true month end dates, try this:
=AND(MONTH(A$1+1)=MONTH(TODAY()),YEAR(A$1+1)=YEAR(TODAY()))

If you don't include the Year then you will highlight the previous month for
every year on your spreadsheet.
 
H

Howard

It worked! Thanks. I had to chage +1 to -1, since I was going back to the
previous month.
 
H

Howard

Oops, it's not working. It highlights the January 31, 2006 column instead of
the December 31, 2005 column.
 
J

JAdamJ

I copied and pasted it exactly as I have it written below, and it's
highlighting the December 31, 2005 column. Are you sure you need to change
the +1 to -1?
 
R

Ron Rosenfeld

In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that
January 2006 has rolled around, I want the column with the date 12/31/2005 to
highlight using conditional formatting.
The Month function works well within the current year
(=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month
(Dec) to highlight when the first month of the current year rolls around?
Click to expand...

Well, if, in fact, the date at the header of the column is the last date of the
month, then:

Cell Value Is Equal To: =TODAY()-DAY(TODAY())
--ron
 
H

Howard

You're right, it works! I must have typed it in incorrectly. So I copied and
pasted.

Thanks,
 
H

Howard

Ron,

Thanks! Works nicely!
--
Howard


Ron Rosenfeld said:
Well, if, in fact, the date at the header of the column is the last date of the
month, then:

Cell Value Is Equal To: =TODAY()-DAY(TODAY())
--ron
Click to expand...
 
R

Ron Rosenfeld

Ron,

Thanks! Works nicely!
Click to expand...

You're welcome. It seems simpler than some of the other solutions, too.

You can also use CF to bold an entire column by using mixed references:

In A1

Format/Conditional Formatting/Formula Is:

=A$1=(TODAY()-DAY(TODAY()))

Then use the format painter to copy that to your entire table.


--ron
 
Top