Calculating the fiscal year

[Peak Seagull]

Hi,

I've attached a spreadsheet where you can see I've successfully added a 'Financial Year' column and the 'Financial Quarter' relating to the 'Financial Year' and 'Date Sold' columns.

I've gone a bit brain dead today but would like to know if/how I can show the year in the 'Financial Year' column as 2010/11, 2011/12, 2012/13 etc to make it easier for the person viewing the final output of the data when used in a dashboard.

Hopefully there is someone clever out there who knows how to do this.

Thanks for your help.
PeakSeagull

 

[pecoflyer]

Perhaps like this and pulled down ?
=YEAR(B4)&"/"&IF(MONTH(B4)<4,4,CEILING(MONTH(B4)/3,1)-1)

 

[Peak Seagull]

Perhaps like this and pulled down ?
=YEAR(B4)&"/"&IF(MONTH(B4)<4,4,CEILING(MONTH(B4)/3,1)-1)

Hi - thanks for the formula it's really clever.

Unfortunately, it's adding the quarter after the year instead of extending the year to the full fiscal year period.

For example, I'd like the financial year column to say:

Now
2010
2011

After
2010/11
2011/12
2012/13
2013/14
etc

 

[vletm]

Peak Seagull
eg for cell F4 =C4 & "/"& RIGHT(C4,2)+1
and etc

 

[bosco_yip]

Or,

In C4, formula copied down :

=YEAR(B4)-(MONTH(B4)<3)&"/"&RIGHT(YEAR(B4)+(MONTH(B4)>3),2)

Regards

 

[Peak Seagull]

Or,

In C4, formula copied down :

=YEAR(B4)-(MONTH(B4)<3)&"/"&RIGHT(YEAR(B4)+(MONTH(B4)>3),2)

Regards

Thank you so much that's brilliant

 

[kamal bhatt]

Or,

In C4, formula copied down :

=YEAR(B4)-(MONTH(B4)<3)&"/"&RIGHT(YEAR(B4)+(MONTH(B4)>3),2)

Regards

If someone puts the date as 14/03/2010, the formula shows the Financial year as 2010/10..

How do one rectify that..

File attached..

 

[bosco_yip]

If someone puts the date as 14/03/2010, the formula shows the Financial year as 2010/10..

How do one rectify that..

File attached..

Change it to :

=YEAR(B4)-(MONTH(B4)<4)&"/"&RIGHT(YEAR(B4)+(MONTH(B4)>3),2)

Regards

 

[Peak Seagull]

Thank you so much that's brilliant

Hi, I've just noticed that using the formula it is doing something strange in March each year as you will see from the attached spreadsheet.
It does the below each year for dates in March - any ideas?

01/03/2012	2012/12
04/03/2012	2012/12
05/03/2012	2012/12
13/03/2012	2012/12
19/03/2012	2012/12
23/03/2012	2012/12
28/03/2012	2012/12
28/03/2012	2012/12
29/03/2012	2012/12

 

[vletm]

Peak Seagull
Wasn't Your original just like below ( reply #3 ) ... for somehow ... year and next years last two numbers ... hmm?

... then what matter of month?

 

[bosco_yip]

Hi, I've just noticed that using the formula it is doing something strange in March each year as you will see from the attached spreadsheet.
It does the below each year for dates in March - any ideas?

01/03/2012	2012/12
04/03/2012	2012/12
05/03/2012	2012/12
13/03/2012	2012/12
19/03/2012	2012/12
23/03/2012	2012/12
28/03/2012	2012/12
28/03/2012	2012/12
29/03/2012	2012/12

As per above Post #08 mentioned the formula in C5 is revised >>

From this :

=YEAR(B5)-(MONTH(B5)<3)&"/"&RIGHT(YEAR(B5)+(MONTH(B5)>3),2)

Into this :

=YEAR(B5)-(MONTH(B5)<4)&"/"&RIGHT(YEAR(B5)+(MONTH(B5)>3),2)

And

The revised formula result as per below screenshot :

 

[Peak Seagull]

That's great works perfectly - thanks
bosco_yip