0

In Excel 2013, I have a date range for the product period:

Column A contains Start Date Eg. 01-AUG-2014 Column B contains End Date Eg. 31-JUL-2015

In Column C I need to map the financial year, which should be 2014-15. Financial year starts on April 1 and ends on March 31 the next year.

My data has financial year from 2001-02 to 2015-16, and currently I write lengthy IF, THEN formulas.

Is there any easier way to do it.

2

This should solve your problem:

=CONCATENATE(YEAR(A1);"-";RIGHT(YEAR(B1);2))
=CONCATENATE(YEAR(A1),"-",RIGHT(YEAR(B1),2))

I am not sure if you need to use , or ; in your Excel version (depends on the Language setting).

edit 1:

=CONCATENATE(YEAR(A2),"-",RIGHT(MAX(YEAR(A2)+1,YEAR(B2)),2))

edit 2:

This will work:

=CONCATENATE(YEAR(A2-DATE(YEAR(D10),4,1)+DATE(YEAR(D10),1,1)),"-",RIGHT(MAX(YEAR(A2-DATE(YEAR(D10),4,1)+DATE(YEAR(D10),1,1))+1,YEAR(B2-DATE(YEAR(D10),4,1)+DATE(YEAR(D10),1,1))),2))

If we would no have leap years we could write -90 instead of -DATE(YEAR(D10),4,1)+DATE(YEAR(D10),1,1) which would simplify the formula:

=CONCATENATE(YEAR(A2-90),"-",RIGHT(MAX(YEAR(A2-90)+1,YEAR(B2-90)),2))
  • My excel uses comma (,) The year after the hyphen should be incremented by 1, where year is same for start and end date – Firee Jan 11 '16 at 11:55
  • 1
    I edited the answer. If A1 contains 01-AUG-2014 and B1 31-JUL-2015 should then be displayed 2014-15 or 2014-16 in C1? – daniel.neumann Jan 11 '16 at 11:59
  • It works ok for those dates, however for 31-MAR-2014 TO 30-MAR-2015, it reflects 2014-15, instead of 2013-14 – Firee Jan 11 '16 at 12:49
  • Updated. For clarification: 31-MAR-2014 TO 30-MAR-2015 should display 2013-14; 30-APR-2014 TO 30-MAR-2015 should display 2014-15; But what should 20-MAR-2014 TO 30-MAR-2015 display? 2013-14 or 2013-15? – daniel.neumann Jan 11 '16 at 13:25
  • 20-MAR-2014 TO 30-MAR-2015 would display 2013-14 – Firee Jan 11 '16 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.