I deal with security clearances and need help with a formula in excel that would help warn me when a security clearance becomes out of date. Security clearances are due every five years, however i would like to be notified 3 months prior to the expiration date that would show up in red. For example, i have a clearance that was granted on 2005 04 20, five years from that date would be 2010 04 20. Any help would be greatly appreciated. Thanks for your time!
If you can use another cell to get the 3 months before expiry date with:
=EDATE(A2,57) where A2 contains the granted date... (Edate is an analysis toolpak addin function... Tools|Addins)
then you can use Conditional Formatting and then apply formula:
=TODAY()>=$B2 where B2 is the cell you have the formula in.
Alternative, skip the EDATE() part and apply Conditional format:
=TODAY()>=DATE(YEAR(A2),MONTH(A2)+57,MIN(DAY(A2),DAY(DATE(YEAR(A2),MONTH(A2)+58,0))))
Where A2 has the grant date.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks