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

1. ## 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. ## 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.

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

#### 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