• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

repeat 3 year time period sequence

jk51

Member
Hi

How to repeat 3 years time period when you drag down the cells? Is there a quick way?
1993-1995
1994-1996
1995-1997
1996-1998
1997-1999 and so on

Thanks

Mr Singh
 
excel option>advance option>general>edit custom list>add>1993-1995,1994-1996,1995-1997>after this add any one value in cell and drag drop ...enjoy
 
Or,

1] Formula way

In A1, enter :

=1992+ROW(A1)&"-"&1994+ROW(A1)

Then copied down

2] Custom Format cells way

In A1, enter : 19931995

In A2, enter : 19941996

Select A1:A2 >> Custom Format cells , enter : 0000-0000, then >> copied down

Regards
 
Last edited:
Thanks INCOMETAX111. Interesting. Its work from 1993-1995 to 1995-1997 but not the next sequence.
I still type long list of year period from 1993-1995 up to current one time then it works.

Mr Singh
i just example it for 3 yrs list you can expand it as required
 
How to repeat sequence if you copy each 3 year time period 5 times?
see example
1993-1995
1993-1995
1993-1995
1993-1995
1993-1995
1994-1996
1994-1996
1994-1996
1994-1996
1994-1996
1995-1997
1995-1997
1995-1997
1995-1997
1995-1997 etc..

Thank you

Mr Singh
 
How to repeat sequence if you copy each 3 year time period 5 times?
see example
1993-1995
1993-1995
1993-1995
1993-1995
1993-1995
1994-1996
1994-1996
1994-1996
1994-1996
1994-1996
1995-1997
1995-1997
1995-1997
1995-1997
1995-1997 etc..

Thank you

Mr Singh
In A1, enter formula and copied down :

=1993+INT((ROW(A1)-1)/5)&"-"&1995+INT((ROW(A1)-1)/5)

Regards
 
In A1, enter formula and copied down :

=1993+INT((ROW(A1)-1)/5)&"-"&1995+INT((ROW(A1)-1)/5)

Regards
Wow. Thank you. Bosco_yip.

Similiar last thing how to repeat sequence if you copy date period 5 times but adding date + 1 after 5 times?
see example
01/01/1993
01/01/1993
01/01/1993
01/01/1993
01/01/1993
01/01/1994
01/01/1994
01/01/1994
01/01/1994
01/01/1994
01/01/1995
01/01/1995
01/01/1995
01/01/1995
01/01/1995 etc.

Thank you.

Mr Singh
 
Wow. Thank you. Bosco_yip.

Similiar last thing how to repeat sequence if you copy date period 5 times but adding date + 1 after 5 times?
see example
01/01/1993
01/01/1993
01/01/1993
01/01/1993
01/01/1993
01/01/1994
01/01/1994
01/01/1994
01/01/1994
01/01/1994
01/01/1995
01/01/1995
01/01/1995
01/01/1995
01/01/1995 etc.

Thank you.

Mr Singh
In A1, formula copied down :

=DATE(1993+INT((ROW(A1)-1)/5),1,1)

Or,

=0+("1/"&1993+INT((ROW(A1)-1)/5))

Regards
 
Back
Top