+ Reply to Thread
Results 1 to 2 of 2

how to convert date to fiscal year if fiscal year start at 16th or 17th of July

  1. #1
    Registered User
    Join Date
    04-28-2013
    Location
    Nepal
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question how to convert date to fiscal year if fiscal year start at 16th or 17th of July

    Hi,
    I have getting problem to show the fiscal year for following date range;
    Date F/Y
    17/07/2006 - 16/07/2007 2006/2007
    17/07/2007 - 15/07/2008 2007/2008
    16/07/2008 - 15/07/2009 2008/2009
    16/07/2009 - 16/07/2010 2009/2010
    17/07/2010 - 16/07/2011 2010/2011
    17/07/2011 - 15/07/2012 2011/2012
    16/07/2012 - 15/07/2013 2012/2013

    what formula should I type in excel to get F/Y for above date range? Please tell me.

  2. #2
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: how to convert date to fiscal year if fiscal year start at 16th or 17th of July

    Place the beginning dates (example 17/07/2006) in a column (example A1:A5).
    Place the end dates (example 17/06/2007) in the next column.
    Place the fiscal year (example 2006/2007) in the next column.
    Define all as the range rngFiscTranslate (example A1:C5).

    Then if you have 01/01/2008 in cell E5, the formula is
    =vlookup(E5,rngFiscTranslate,3)

    You must type in the 2 date columns so Excel recognizes them as text. If you don't want to do that because you pasted the dates in, you can use the DATEVALUE function to convert to dates.

    You should decide if "#N/A" is acceptable for dates earlier than the lowest date in your range. You should also decide how to handle dates higher than the highest date in your range. For example if you want to answer 0 for low dates, go
    =if(E5<MIN(rngFiscTranslate),0,vlookup(E5,rngFiscTranslate,3))
    Or you can just use =vlookup(E5,rngFiscTranslate,3) if it gives acceptable results.
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.

+ 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