How to repeat the financial year like 2006-07, 2007-08,etc

[VDS]

@ Dear all,

For data compilation of balance sheet for different financial years, I want to make report as per the attached format.

A1=B1=C1=D1=E1=F1=G1
2006-07 = 2007-08 = 2008-09=2009-10= 2010-11= 2011-12= 2012-13

=rows(A$1:A1)+2000&"-0"&ROWS(A$1:A1)+1. This function works upto "2008-09".
for next financial year say, 2009-10, the 0 marked in italics become extra, deletion within formula become slightly difficult.

Secondly, it works row wise. How it can be worked column wise ?

Is anybody can help me with better solution / macro.


VDS

 

[Abhijeet R. Joshi]

Add the if function to consider rows above 9, use the below formula:
=ROWS(A$1:A1)+2000&IF(ROWS(A$1:A1)<9,"-0"&ROWS(A$1:A1)+1,"-"&ROWS(A$1:A1)+1)

Another approach would be:
Mention 2001-02 in cell A1 and then in cell A2 or B1 mention the below and drag columnwise or row-wise as required
=LEFT(A1,4)+1&IF(RIGHT(A1,2)<"09","-0"&RIGHT(A1,2)+1,"-"&RIGHT(A1,2)+1)

The benefit of the second approach is that this can be used row wise as well as column wise..

Hope this helps..

 

[BBD]

Hi VDS,

A shorter one if it looks pretty.

=LEFT(A1,4)+1&"-"&TEXT(RIGHT(A1,2)+1,"00")

Ofcourse the starting point needs to be hardcoded similar to Abhijeet's suggestion above. (2001-02 in cell A1).

Cheers,
BD

 

[VDS]

@Abhijeet/BBD, this is marvelous. Small doubt. Shall it be done backwards also like 2008-09, 2007-08, 2006-07 ?


VDS

 

[BBD]

Hi,

Just tweak the formula to this.

=LEFT(C3,4)-1&"-"&TEXT(IF(RIGHT(C3,2)="00",99,RIGHT(C3,2)-1),"00")

Cheers,
BD

 

[Debraj]

another approach..

=TEXT(LEFT(A2,4)-1,"0000")&"-"&RIGHT(LEFT(A2,4),2)

You can remove colored one.. if you don't have any date like 1001-02

 

[VDS]

@ Hai,

That's wonderful. This forum is so awesome.

VDS

 

[SirJB7]

Hi, VDS!

I'd go for this:
A1: starting year
B1:Z1 : =$A1+COLUMNA()-2&"-"&DERECHA($A1+COLUMNA()-1;2) -----> in english: =$A1+COLUMN()-2&"-"&RIGHT($A1+COLUMN()-1,2)
It works for any starting year, checked with 1991 thru 2016 (Z1).

That's for column wise, if you need row wise just change the COLUMN function by ROW function.

Regards!