Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default EOMONTH, TODAY() and Conditional Formatting (Excel 2003)

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default EOMONTH, TODAY() and Conditional Formatting (Excel 2003)

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

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

"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


"Mrs Dumm" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting on 'Today' Madame Artois Setting up and Configuration of Excel 1 June 20th 07 05:19 PM
conditional formatting: problem entering EOMONTH formula... MeatLightning Excel Discussion (Misc queries) 0 February 6th 06 09:35 PM
conditional formatting: problem entering EOMONTH formula... Jonathan Cooper Excel Discussion (Misc queries) 0 February 6th 06 09:34 PM
conditional formatting: problem entering EOMONTH formula... Jonathan Cooper Excel Discussion (Misc queries) 1 February 6th 06 09:28 PM
Conditional Formatting ... Today() & More Ken Excel Worksheet Functions 1 February 9th 05 02:50 PM


All times are GMT +1. The time now is 04:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"