Formula for Dates beyond a certain time frame
Hello!
I'm trying to put together a spreadsheet where dates are a big issue. Anytime a piece of equipment is beyond the 5 year mark it will need to be replaced. Is there any way that excel can automatically highlight a cell when it is beyond the 5 year mark of its user date? I'm still a beginner at excel
yes, you can use conditional formatting for this. if you are unfamiliar with CF, upload a sample and i can help you. otherwise, high-light the range with the dates you want to test for and in CF add a new rule with this formula
=DATE(YEAR(NOW())-5,0,0)<C2 (this assumes that the equipment date is in cell C2
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this:
Select the dates you want to highlight. Click the conditional formatting tool, "Highlight Cells Rules", "More Rules", "Use a formula to determine which cells to format", and enter the following into the formula bar: (assuming A2 is the top left corner of your date range)
=A2<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))
Choose the format you want to apply to the dates older than 5 years and click OK.
Also,
to avoid blanks If there is, you can use AND
=AND(N(B2),DATEDIF(B2,TODAY(),"Y")>=5)
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Or
=AND(N(B2),B2<EDATE(TODAY(),-60))
the -60 represents 60 months ago
I have tried and it still doesn't seem to be working for me... not sure what I am doing incorrectly.
i don't think you can use edate(or any of the functions in the anaysis toolpak) in cf like that
to use it you have to define it as a named formula
ie define name
"martin" refers to =edate(today(),-60)
then in cf use
=AND(N(a2),a2<martin))
Last edited by martindwilson; 06-04-2012 at 08:23 AM.
"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
Here is your file with Conditional Formatting applied to column A. Any date more than 5 years old will highlight in blue. (There is only 1 - the first entry.)
interesting that works but as soon as i look at cf if tells me i cant refer to other sheets and doesnt work.wonder if its coz im using '97 at the moment and edate is in the tool pak,
The "can't refer to other sheets" issue would be because it's a new feature for 2010 (but the CF isn't referring to another sheet) so....???
The EDATE() not being available in '97 should cause a #NAME error.
That is the actual error that pre 2007 versions gives even for the ATP formulas... that's probable the default error for ... "it's not working, try something else...."
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
And I just noticed the file type doesn't match the profile. I based it on profile.
Re: Formula for Dates beyond a certain time frame
Thank you so much guys! I really appreciate everyone's help!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks