Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula for sequential years (any)

    Hmmm. For about the first time ever, it was easier than I thought... Thanks!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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