ExcelBanter - Fiscal Year Calculation

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Fiscal Year Calculation (https://www.excelbanter.com/excel-worksheet-functions/69451-fiscal-year-calculation.html)

DaGo21

Fiscal Year Calculation

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!

--
DaGo21
------------------------------------------------------------------------
DaGo21's Profile: http://www.excelforum.com/member.php...o&userid=31167
View this thread: http://www.excelforum.com/showthread...hreadid=508279

qwopzxnm

Fiscal Year Calculation

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

--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
View this thread: http://www.excelforum.com/showthread...hreadid=508279

Pete

Fiscal Year Calculation

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

Fiscal Year Calculation

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
------------------------------------------------------------------------
DaGo21's Profile: http://www.excelforum.com/member.php...o&userid=31167
View this thread: http://www.excelforum.com/showthread...hreadid=508279

Pete

Fiscal Year Calculation

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

Fiscal Year Calculation

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 ;)

--
DaGo21
------------------------------------------------------------------------
DaGo21's Profile: http://www.excelforum.com/member.php...o&userid=31167
View this thread: http://www.excelforum.com/showthread...hreadid=508279

Pete

Fiscal Year Calculation

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

Fiscal Year Calculation

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"

--
DaGo21
------------------------------------------------------------------------
DaGo21's Profile: http://www.excelforum.com/member.php...o&userid=31167
View this thread: http://www.excelforum.com/showthread...hreadid=508279

daddylonglegs

Fiscal Year Calculation

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")

--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=508279

DaGo21

Fiscal Year Calculation

daddylonglegs Wrote:
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")

This works great, however it does not take into account the fiscal year
calculation, so 1/8/2004 and 1/2/2005 are in 2 different fiscal years
with the above function, however in real fiscal years they both should
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 - sorry

--
DaGo21
------------------------------------------------------------------------
DaGo21's Profile: http://www.excelforum.com/member.php...o&userid=31167
View this thread: http://www.excelforum.com/showthread...hreadid=508279

DaGo21

Fiscal Year Calculation

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?

--
DaGo21
------------------------------------------------------------------------
DaGo21's Profile: http://www.excelforum.com/member.php...o&userid=31167
View this thread: http://www.excelforum.com/showthread...hreadid=508279

daddylonglegs

Fiscal Year Calculation

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

--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=508279

daddylonglegs

Fiscal Year Calculation

DaGo21

Fiscal Year Calculation

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

It works brilliant! Thanks a lot!

--
DaGo21
------------------------------------------------------------------------
DaGo21's Profile: http://www.excelforum.com/member.php...o&userid=31167
View this thread: http://www.excelforum.com/showthread...hreadid=508279

All times are GMT +1. The time now is 10:15 AM.