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 date
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.

 

[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

 

[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]

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]

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 giv
the correct financial YEAR

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

An alternative without EDATE...

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

This works great, however it does not take into account the fiscal yea
calculation, so 1/8/2004 and 1/2/2005 are in 2 different fiscal year
with the above function, however in real fiscal years they both shoul
be in FY05 (using European Date Format (dd/mm/yyyy) here).

Anyway I could combine those 2 functions into 1?

NOTE: in my previous post, I copied twice the same function - sorr

 

[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 year
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 contain
2nd February 2001 it will also return "FY05".

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

 

[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!