Fiscal Year Calculation

[DaGo21]

Hello,

I have an issue within Excel and I really hope you can assist me in cracking it. Let me try to describe my problem;

I have a given date, e.g. January 22, 2001
With this date I need to add 4 years, so I simply did cell * 1460 (as that is 4*365), so I end up with January 21, 2005. So far so good...

Now I want to know the Fiscal Year which this month is in.
Example, fiscal year 06 is from June 1, 2005 - May 31, 2006.

This means that I need some kind of calculation to see in which Fiscal Year this date is. In my above example (January 21, 2005) this is FY06, but June 1, 2006 would be FY07.

How can I do this? I experimented with using Year() and Month() and then do some logical check with IF, but I can't crack it... Who could help???

When possible the solution should not have hardcoded years, so even if I enter a date in August 2050 it should still say FY51.

Thanks!

[qwopzxnm]

I think there is a better way to calculate 4 years from a date...

If cell A1 = January 22, 2001

You should put this formula in another cell.

=date(year(A1)+4,month(A1),day(A1))

Also, how are your fiscal years calculated? How would I get a datee range for FY 2006, FY 2007, etc...

[Pete]

Assuming your date is in cell A1, this formula will return the
financial year as a number (i.e. I have not formatted it to return
FY06):

=YEAR(A1-151)-1999

Ist June is 151 days into the year, so taking this away from the date
and then taking 1999 away will return the values you want. I've not
tested this many years into the future - I've just realised it doesn't
take account of leap years.

Anyway, hope it helps for the moment.

Pete

[DaGo21]

Thanks for your fast reply;

Sorry but I seem not to understand your question; however I hope to answer it the best way I understand it;

FY06 = 01/06/2005 - 31/05/2006
FY07 = 01/06/2007 - 31/05/2007
etc.

What I would like is to see simply FY07 after the "trick"
Sorry I'm not to familiar with Excel, hope it's not a too dump question...

Oh during my Google experience I found =MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1
seems not to work

Thanks again and for your answer on the 4 years, never thought about that.

[DaGo21]

Hi Pete,

Thank you too, I worked a bit with it and it seems to get me somewhere, I formated the cell with "custom" and entered "FY"00 This shows in Excel for example FY05 (if date was for example 22/Jan/2004)

Are there better ways? I am at home so got to wait until monday, but I can't leave my issue alone

[Pete]

Ok, here's an amended formula which does take account of leap years:

=YEAR(A1-151-INT((YEAR(A1)-2000)/4))-1999

Hope this helps.

Pete

[Pete]

Hi,

If you really want it to display as "FY06", then put cursor on the cell
with the formula in and click Format | Cells | Number (tab) then select
Custom from the drop-down list and enter:

"FY"#00

The underlying value is still a number.

Hope this helps.

Pete

[DaGo21]

Great this works...

Silly question, but would it be possible to have both aswers intergrated so I only need use 1 cell?

So the =date(year(A1)+4,month(A1),day(A1)) and the =date(year(A1)+4,month(A1),day(A1))

Again thanks a lot for your support!!

BTW; I also have dates back in 1998 or so, those seems not to work, they show for example -FY01, because of the "1999"

[daddylonglegs]

Your easiest way is to use EDATE which is part of Analysis ToolPak

this formula combines both functions you need, to add 4 years and give the correct financial YEAR

=TEXT(EDATE(A1,55),"F\YY")

An alternative without EDATE...

=TEXT(DATE(YEAR(A1),MONTH(A1)+55,1),"F\YY")

[DaGo21]

Thanks all,

I'm now using the =TEXT(DATE(YEAR(A1),MONTH(A1)+55,1),"F\YY") and that works ok for me. The only issue I have is that it does not take into account the fiscal years. So this means that 8/Aug/2004 is different then 2/Feb/2005. In "real" they should be in FY05, however in the way it been used now I get FY04 and FY05.

Is there a way to incorporate the "if bewteeen 1/Jun/xx and 31/May/xx = FYxx"?

Thanks once more, I really appreciate your help!

[DaGo21]

Perhaps stupid question, but would it be possible via a Macro or so?
I'm almost in a stage to dump it to SQL and query from there - shame it takes so much time

Anyone new ideas?

[daddylonglegs]

I'm not sure I understand the problem

=TEXT(DATE(YEAR(A1),MONTH(A1)+55,1),"F\YY")

does take into account the fiscal years. It's designed to add 4 years to your date and then give the correct fiscal year for that date. E.g. If A1 contains 8th August 2000 it will return "FY05". If A1 contains 2nd February 2001 it will also return "FY05".

If that isn't what you wanted please explain your requirement again

[DaGo21]

Apologies!! My bad, I did it wrong myself...

It works brilliant! Thanks a lot!