• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula to display fiscal years

J

Jagdev Singh

Active Member
Hi Experts

Could you please help me to display the fiscal years for the dates. The Policy period we have is 31st march to 30 April on the basis of date.

Ex for a date –

16/12/2011 – Should display - 2011/2012

04/01/2012 - 2011/2012

11/01/2012 - 2011/2012

Regards,

JD
 
Not sure how your dates work, as March 31 to April 30 is a 1 month gap. Perhaps you meant April 1 is the transition date? If so, a formula like so would work:
=IF(MONTH(A2)>=4,
YEAR(A2)&"/"&YEAR(A2)+1,
YEAR(A2)-1&"/"&YEAR(A2))

Change the beginning condition to a different month number if April is not the transition month.
 
Hi Luke

I was thinking for wring a VBA code which will help me to do this calculation on a click. I am stuck with one thing in many of other cases the fiscal year varies in its months pattern like Jan-Dec and etc…How can we cover random start and end year concept.

Regards,
JD
 
Can you elaborate on what you mean by a "random start and end"?
The idea of my original formula would be the same...you need to check
IF(Value >= StartOfFiscal,
Current Year& "/"& Next Year,
PrevYear& "/"& This Year)

Would have to know more about how the StartOfFiscal gets defined.
 
But for each cell, how do you know when the start point is? If it's in a cell somewhere, like running along the top of the sheet, I suppose you could do:
=IF(Value>CriteriaCell,
First Option,
Second Option)

But again, need to know how/what is controlling the start point. Otherwise, we might as well do:
=IF(Value>RANDBETWEEN(1,50000),...)
:p
 
Hi Luke

Just was thinking something like the attached sample file.

Regards,
JD
 

Attachments

  • Sample.xlsx
    8.8 KB · Views: 2
Hi Luke

I think the start and end date consept will not work as per the year is concerned. I added few raw data in the attached sample file. The year may vary and it is not fix.
 

Attachments

  • Sample LM.xlsx
    9.5 KB · Views: 3
1. Don't really need an end date, just the start date is important
2. In your example, my formula still works. That's why my example listed multiple years. If wrong, perhaps you can provide a larger list showing inputs and their respective desired outputs.
 
You must log in or register to reply here.
Back
Top