January 2006

[Guest]

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?

 

[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

 

[pinmaster]

Nevermind, I guess it doesn't work

 

[pinmaster]

Try this:

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

HTH
JG

 

[Guest]

Thanks! I knew I had to get the year in there somewhere, but I didn't know how.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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?

 

[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?

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

 

[Guest]

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

Thanks,

 

[Guest]

Ron,

Thanks! Works nicely!
--
Howard

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

 

[Ron Rosenfeld]

Ron,

Thanks! Works nicely!

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