Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DaGo21
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
qwopzxnm
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DaGo21
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete
 
Posts: n/a
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DaGo21
 
Posts: n/a
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete
 
Posts: n/a
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DaGo21
 
Posts: n/a
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DaGo21
 
Posts: n/a
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DaGo21
 
Posts: n/a
Default 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

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DaGo21
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ho to Delete "Ghost" Pivot Tables needyourhelp Excel Discussion (Misc queries) 3 November 17th 05 11:10 PM
Calculation with Working day of the year Box666 Excel Discussion (Misc queries) 4 November 10th 05 07:33 PM
Is the IRR calculation based on cash flows at beginning of year? sammad Excel Worksheet Functions 1 September 6th 05 05:15 PM
Date formula: return Quarter and Fiscal Year of a date Rob Excel Discussion (Misc queries) 7 May 11th 05 08:48 PM
Fiscal year total from running 12 months Excel Worksheet Functions 2 February 9th 05 01:11 AM


All times are GMT +1. The time now is 03:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"