EOMONTH, TODAY() and Conditional Formatting (Excel 2003)

[Mrs Dumm]

All,
This is driving me crazy...

I have dates in cells. I want them to turn pretty colors when they are
actually expired, are going to expire in the current month, and are going to
expire next month. Conditional formatting is the ticket... (Excel 2003)

However, the date in the cells is not really the actual expiration date in
all cases. There are some licenses that expire at the END of the month. So,
if I was certified on July 3, 2008, it would actually expire July 31, 2009
(this month). I would put the actual expiration date in the cell (July 31,
2009) instead of 1 year from the date (July 3, 2009) but when I have to
research it, knowing the date the test was actually taken is invaluable.

So, silly me, I thought, well, I'll just round up the date in the cell using
EOMONTH and then compare that date with using EOMONTH on TODAY() to see if
they match.

I have tried every which way and whenever I use EOMONTH on the cell or
TODAY(), I get "You may not use references to other worksheets or workbooks
for Conditional formating criteria.

I've tried:
=[cell]<EOMONTH(TODAY(), 1)
(if the date in the cell is less than the end of the month of today plus a
month, then turn the cell pink). This should make any cell that has a value
less than 8/31/09 turn pink.

I can put the formula in a cell: =EOMONTH(TODAY(), 1) which returns
"8/31/2009" and then reference that particular cell in the conditional
formatting and it works. (=[cell]<[$result$cell])

I was using =[cell]<(TODAY()+45) to get it turn turn colors when the
expiration date is 45 days out but since they expiration date for someone who
took the text at the beginning of the month (8/3/08) and the end of the month
(8/30/08) are exactly the same (8/31/09), the 45 days is misleading. (I
won't even go into =[cell]>TODAY() ) and how 7/3/09 looks expired while
7/20/09 doesn't even though they both actual expire at the end of the same
month.

Please, oh please, what the heck am I missing? My brain is now completely
fried...

Thanks,
Alicia

 

[T. Valko]

Please, oh please, what the heck am I missing?

Excel told you what the problem was. You just didn't pay attention!

"You may not use references to other worksheets or
workbooks for Conditional formating criteria."

What that means is, EOMONTH is part of the Analysis ToolPak add-in which is
another workbook. So, that's why Excel complains.

Here's a work-around for the EOMONTH function based on today's date:

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)


--
Biff
Microsoft Excel MVP

All,
This is driving me crazy...

I have dates in cells. I want them to turn pretty colors when they are
actually expired, are going to expire in the current month, and are going
to
expire next month. Conditional formatting is the ticket... (Excel 2003)

However, the date in the cells is not really the actual expiration date in
all cases. There are some licenses that expire at the END of the month.
So,
if I was certified on July 3, 2008, it would actually expire July 31, 2009
(this month). I would put the actual expiration date in the cell (July
31,
2009) instead of 1 year from the date (July 3, 2009) but when I have to
research it, knowing the date the test was actually taken is invaluable.

So, silly me, I thought, well, I'll just round up the date in the cell
using
EOMONTH and then compare that date with using EOMONTH on TODAY() to see if
they match.

I have tried every which way and whenever I use EOMONTH on the cell or
TODAY(), I get Please, oh please, what the heck am I missing?>
I've tried:
=[cell]<EOMONTH(TODAY(), 1)
(if the date in the cell is less than the end of the month of today plus a
month, then turn the cell pink). This should make any cell that has a
value
less than 8/31/09 turn pink.

I can put the formula in a cell: =EOMONTH(TODAY(), 1) which returns
"8/31/2009" and then reference that particular cell in the conditional
formatting and it works. (=[cell]<[$result$cell])

I was using =[cell]<(TODAY()+45) to get it turn turn colors when the
expiration date is 45 days out but since they expiration date for someone
who
took the text at the beginning of the month (8/3/08) and the end of the
month
(8/30/08) are exactly the same (8/31/09), the 45 days is misleading. (I
won't even go into =[cell]>TODAY() ) and how 7/3/09 looks expired while
7/20/09 doesn't even though they both actual expire at the end of the same
month.

Please, oh please, what the heck am I missing? My brain is now completely
fried...

Thanks,
Alicia