Saturday, February 4, 2012

Formula To Convert Date To Financial Year In MS Excel (07-Feb-1987 = FY 1986-87)

Today one of my friend asked me, the Formula to converts Date into Financial Year in MS Excel. I thought it is better to explain the same in my blog for the interest of other visitors.

Financial Year is 12 Months period which usually the Company follows according to their Accounting Policy. Here, I am going to explain you the Formula for the Financial Year which starts from the 1st Day of April Month and ends on 31st Day of March Month in the Following Year.

For Example, the Financial Year of 04 February, 2012 is 2011-12


Formula :

=IF(MONTH(A1)>3,"FY "&YEAR(A1)&"-"&RIGHT(YEAR(A1)+1,2),"FY "&YEAR(A1)-1&"-"&RIGHT(YEAR(A1),2))

Functions used in the Formula?


The formula consists of the following Excel Functions

  1. Logical Function - IF
  2. Date Functions - Month & Year
  3. Text Function - Right
How the Formula Works?

In Simple Text..

     If the Month number of the Date is Greater Than 3 (three).. then the result should be the Year of the Date and Following Year of Date or else, the result should be the Prior Year of the Date and Year of the Date. Of course, Hyphen symbol (-) between these two years.

   RIGHT Function in the Formula is just to format the result. If we want the result as FY 2010-2011 then RIGHT function is not required. But we want to show the result as FY 2010-11 then we have to use Right Function (How to use Right Function in MS Excel). Right Function will results the last two characters of the second part of the result.

    & symbol  in the Formula is to combine all parts of the result. Even you can use CONCATENATE Function for the same result.


16 comments:

  1. Dear Friend,
    I stand here with a problem to be solved.

    I have two (trial balances)excel sheets with same figures but while entering data I missed out some figures in 2nd sheet which was entered manully.

    so my problem is how to solve this problem.

    Thanks in advance

    Anupama Asst.Manager (F & A)

    ReplyDelete
  2. Dear Anupama,
    I think you are talking about the effect on 2nd sheet , while editing the first one. If so, kindly check whether both the sheet has been selected, change made in one sheet will affect the same cell in 2nd sheet, Deselect the sheet by clicking on the sheet name

    ReplyDelete
  3. Thank you for a great post. Worked wonders in minutes.

    ReplyDelete
  4. Very useful, saved me lots of time - many thanks!

    ReplyDelete
  5. Very useful post. I used the same formula for my MS Access Report to get the financial Year. I replaced IF with IIF. Thank you very Much

    ReplyDelete
  6. REALLY HELP ME A LOT.... IN MY OFFICIAL WORK.. THANK YOU FOR THIS POST

    ReplyDelete
  7. Works like dream... .. Thanks.

    ReplyDelete
  8. Excellent !
    Thanks. Saved me a lot of time.

    ReplyDelete

Share your comment here..