Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Years calculation (2003)

    Hi there
    I want to put a date (day month year) on every column ... In one cell I have a date .. 1 June 2009.. In the next cell I put =a1+365. That works ok until you hit leap years. I have to do 100 years.
    Is there a formula to do this ???? Because after 80 years it jumps to May.

    Thanks

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years calculation (2003)

    Probably a better way, but if date is in A1 formula in A2

    =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

    and copy down

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Years calculation (2003)

    Thanks heaps

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

    Re: Years calculation (2003)

    You can also do this without formula:
    In the first cell, enter 1 June 2009.
    In the cell below it, enter 1 June 2010.
    Select these two cells, then fill down for another 98 cells by dragging the fill handle in the lower right corner.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Years calculation (2003)

    Just as an addition to Hans' answers you could use the little used function Edate. To do this you need the Analysis Toolpak installed.

    In A1 type in 1/6/2008 then in A2 type =EDATE(A1,12) and then fill down.

    Just another option <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Years calculation (2003)

    One more way, A2 entered formula and then fill down :

    =EOMONTH(A1,11)+1

    Regards
    Bosco

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years calculation (2003)

    A shorter keystroke

    enter 1 Jun 2009
    right click the cell and fill down
    choose Fill Years

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Years calculation (2003)

    Thanks everybody....

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years calculation (2003)

    In teaching any Windows-based application, I tell my clients "There are 4 ways of doing everything", and "If you don't know all 4, you probably aren't using your computer efficiently",
    but FIVE ways to fill succesive years?
    You guys take the cake! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

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

    Re: Years calculation (2003)

    > You guys take the cake!

    Cake and Excel - see the thread starting at <post:=758,159>post 758,159</post:>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years calculation (2003)

    > Cake and Excel - see the thread starting at post 758,159

    Crumbs!
    And all these years i thought it was cookies!

Posting Permissions

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