LinkBack URL
About LinkBacks
Hello,
I need to format a column of cells to highlight red when the date (which is formatted to look like 15-Nov-10) is greater than 1 year. Any help would be appreciated. Thanks.
Hello,
I need to format a column of cells to highlight red when the date (which is formatted to look like 15-Nov-10) is greater than 1 year. Any help would be appreciated. Thanks.
greater than one year from when?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
What does it mean for a date to be "greater than 1 year"? Do you mean 1 year greater than the current date?
Hi,
See the very similar post I responded to earlier today.
http://www.excelforum.com/excel-gene...ecome-red.html
Rgds
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Yes the current date. Example: today is 21-Nov-10, any date 21-Nov-09 or greater should be highlighted red.
See Richard Buttrey's linked post but instead of "=7" use "=365". And also provide the version of Excel you are using; the functionality and interface for conditional formatting in Excel 2007 and beyond is quite different than 2003 and prior.Originally Posted by jshpik1
what about leap years?
B1>=EOMONTH(EOMONTH(A1,-1)+1,11)+DAY(A1)
mind you you might have to make that a named formula
Last edited by martindwilson; 11-22-2010 at 04:17 AM.
I've tried the formula at Richard's link and it doesn't seem to be working. I'm using Microsoft Excel 2007. So I go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. I then paste this formula in the box that states "Format values where this formula is true"
=TODAY()-A1>=365
Click format and change the fill to red then hit ok. I've also change the format of the cells to hold that it should be a date which looks like 24-Nov-10. However on testing the formula I've entered 23-Nov-09 into the box. In this case the cell with the date 23-Nov-09 should be highlighted, and 25-Nov-09 should not be highlighted. Nothing happens. All that happens is the formula highlights some random row. Also if there is no value in the box, the conditional formating should do nothing. It doesn't need to be so accurate as to include leap years. Thanks.
Sorry, it shouldn't be cell A1 that is highlighted. These dates are in column D and E rows 4 - 252.
Still not sure of your requirement, should 1st Jan 2010 be highlighted, it fulfills the requirement "any date 21-Nov-09 or greater should be highlighted red"?
....or do you want to highlight dates earlier than 21 Nov 2009, e.g. 1st Jan 2009?
Audere est facere
mine works fine lol
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks