Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: increment date by a whole year

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Evening All,

    on behalf of Jack in the UK :

    what's the formula for incrementing a cell with a date in it by a whole year (taking into account leap years)

    so : 31/07/2002 plus a year would always be 31/07/2003

    scource cell is formatted as a date dd/mm/yyyy - I can't just add 365 as this will trip up on leap years

    thanks
    Jack in the UK

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Chris im home now, sunny South London,

    Also would add needs to be able to edit to say any add 1 4 7 years on to the date the leap is causing me headaces..
    Cheers again Chris/


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =EDATE(TODAY(),12)
    =EDATE(TODAY(),48)
    =EDATE(TODAY(),84)

    Note: EDATE is supplied by the Analysis ToolPak add-in.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Use the DATE function. With A1as reference cell:

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

    This will handle leap years correctly.

    Bye,
    Jay

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    guys

    thank you anf also thank you, i have had a hell of a day, and my MD asked me this at 5.02 i was shattered, i do two jobs in one

    thank you i still am brain dead.. thank you.

    ******************CHRIS my graet friend THANK YOU!************* small things mean so much to me, thanks ...

    Your a fine friend.....

    _________________
    If you can help a guy in trouble -
    If you can sort that nagging problem -
    Pease try, at home, at work or on a message board.

    Others help you!
    So PLEASE help if you can - If only the once.

    Thank you -

    Rdgs
    ======

    [ This Message was edited by: Jack in the UK on 2002-04-25 11:21 ]

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As I'm sure XL does, use the technically correct number of days in any given year:

    365.2422

    _________________
    Cheers, NateO
    "Me no are no nice guy."

    [ This Message was edited by: NateO on 2002-04-25 11:54 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 11:53, NateO wrote:
    As I'm sure XL does, use the technically correct number of days in any given year:

    365.2422

    _________________
    Cheers, NateO
    "Me no are no nice guy."

    [ This Message was edited by: NateO on 2002-04-25 11:54 ]
    Excel supports the Gregorian calendar which expresses years in terms of whole days...


    Main Entry: Gregorian calendar
    Function: noun
    Date: circa 1771
    : a calendar in general use introduced in 1582 by Pope Gregory XIII as a revision of the Julian calendar, adopted in Great Britain and the American colonies in 1752, marked by the suppression of 10 days or after 1700 11 days, and having leap years in every year divisible by four with the restriction that centesimal years are leap years only when divisible by 400

    [ This Message was edited by: Mark W. on 2002-04-25 12:01 ]

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    And the Vikings:









    English Days


    Sunday


    Monday


    Tuesday


    Wednesday


    Thursday


    Friday


    Saturday


    Old Norse Days


    Sunnudagr


    Mánadagr


    Tısdagr


    Óğinsdagr


    Şórsdagr


    Frjádagr


    laugardagr


    *


    Rough Translation


    Sunna's day


    Manni's day


    Tyr's day


    Odin's day


    Thor's day


    Frigga's day


    Satyr's day




    *




    In any case, my formula does not work Best to stick with Jay's function.
    _________________
    Cheers, NateO
    "Me no are no nice guy."

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...Or, with 1 function call using EDATE.

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or that



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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