January 2006
 

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

January 2006
 

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

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

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497695

January 2006
 

Nevermind, I guess it doesn't work!


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497695

January 2006
 

Try this:

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

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497695

January 2006
 

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


"pinmaster" wrote:


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

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

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497695

January 2006
 

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.

"Howard" wrote:

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

January 2006
 

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


"JAdamJ" wrote:

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.

"Howard" wrote:

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

January 2006
 

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


"JAdamJ" wrote:

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.

"Howard" wrote:

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

January 2006
 

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?

"Howard" wrote:

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


"JAdamJ" wrote:

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.

"Howard" wrote:

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

January 2006
 

On Tue, 3 Jan 2006 13:31:01 -0800, Howard
wrote:

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

January 2006
 

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

Thanks,
--
Howard


"JAdamJ" wrote:

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?

"Howard" wrote:

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


"JAdamJ" wrote:

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.

"Howard" wrote:

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

January 2006
 

Ron,

Thanks! Works nicely!
--
Howard


"Ron Rosenfeld" wrote:

On Tue, 3 Jan 2006 13:31:01 -0800, Howard
wrote:

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

January 2006
 

On Tue, 3 Jan 2006 19:28:02 -0800, Howard
wrote:

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