# Thread: Formula for sequential years (any)

1. ## Formula for sequential years (any)

A question for anyone with more than a few weeks experience of Excel!

I want to create a column with dates, where each cell is one year on from the one above, using a formula/function, in pseudocode as "=cell_above+1_year".

Suppose cell A1 contains 01/04/2003 (British dd/mm/yyyy format).
I want cell A2 to contain 01/04/2004
cell A3 to contain 01/04/2005
and so on.

Easy? Thanks!

2. ## Re: Formula for sequential years (any)

Enter the first two dates manually, e.g. 01/04/2003 in A1 and 01/04/2004 in A2.
Select A1 and A2.
The small black square in the lower right corner of the selection is called the fill grip. If you hover the mouse pointer above this fill grip, the pointer changes into a solid black cross. Drag the fill grip down as far as needed.

3. ## Re: Formula for sequential years (any)

Brilliant! I've never even heard of a Fill Grip! Saves developing a nasty formula... <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

4. ## Re: Formula for sequential years (any)

If you're interested, play around with it. If you drag the fill grip with the right mouse button, you'll get all kinds of extra options, depending on the kind of data.

5. ## Re: Formula for sequential years (any)

Hmmm. For about the first time ever, it was easier than I thought... Thanks!

6. ## Re: Formula for sequential years (any)

<hr>Saves developing a nasty formula<hr>
But if you're feeling nasty <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

Of course, if you have Feb 29 and you want to control for leap years the fill handle works OK if that date occurs in the first year. Otherwise the formula does get rather more complex ...
=MIN(DATE(YEAR(\$A\$1)+ROW()-1,MONTH(\$A\$1),DAY(\$A\$1)),DATE(YEAR(\$A\$1)+ROW()-1,MONTH(\$A\$1)+1,0))
Again, this only works if Feb 29 is in A1.

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•