+ Reply to Thread
Results 1 to 3 of 3

Determine if date is before end of fiscal year of previous year

  1. #1
    Registered User
    Join Date
    12-10-2010
    Location
    San Diego, California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Determine if date is before end of fiscal year of previous year

    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.
    Last edited by ccarver; 12-13-2010 at 11:56 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Determine if date is before end of fiscal year of previous year

    Perhaps you mean something like:

    Please Login or Register  to view this content.
    the above will determine the last instance of Sep 30th that occurred prior to today's date backdated 1 one year
    where the resulting date exceeds that listed in A1 etc that is flagged as being "out of regulation"

    using your example dates and today's date - the Sep 30th date occurs in 2009 and given the original Aug 15th 2009 occurs prior to that date then this means it is deemed "out of regulation" (the same date in 2010 would be "OK").

  3. #3
    Registered User
    Join Date
    12-10-2010
    Location
    San Diego, California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Determine if date is before end of fiscal year of previous year

    That works perfectly. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1