Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting on 'Today' | Setting up and Configuration of Excel | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
Conditional Formatting ... Today() & More | Excel Worksheet Functions |