There is a column of dates of certification that must be done every fiscal year. If an employee does not certify that fiscal year, he/she is out of regulation. I'm attempting to determine from that list of dates, which dates are before the end of the fiscal year (fiscal year ends September 30) of the previous year. To clarify, if an entire fiscal year passes without the recertification, I need to track that by highlighting the field (preferably in red). This will determine who is out of regulation.
I've attempted using TODAY()-365, but then I fall short because I need the September 30 prior to TODAY()-365. The equation needs to solve the date one year ago from today, then the September 30 before that date. Hopefully it can give me those dates that are on or before September 30. Example: 15 August 2009 would be before the date, but 15 August 2010 would not.
Thank you for your help.
Bookmarks