+ Reply to Thread
Results 1 to 8 of 8

Formula to add specific amount of years to a date

  1. #1
    Registered User
    Join Date
    04-12-2005
    Posts
    2

    Arrow Formula to add specific amount of years to a date

    Cell A1 has a date of, for example, 12/04/2005. I would like to put a formula in Cell B1 which will add 65 years to the date on A1, and should therefore = 12/04/2070.

    Thus, when I enter dates down Column A, I can drag the formula down from B1, and Excel will automatically add 65 years to every date in Column A.

    Effectively, this formula is A + 65 years = B. Unfortunately, Bill Gates doesn't agree!

    Please can someone help me?

  2. #2
    Registered User
    Join Date
    04-13-2004
    Posts
    14
    First, here is text from the help screen on dates

    Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

    So your formula could be: =A1+(365*65), since each day equals to 1.

  3. #3
    Registered User
    Join Date
    04-12-2005
    Posts
    2
    Thanks Diver. Problem is, if I use the formula you have provided here, the date is 65 years in the future - but not to the month and date; which is probably because of leap years involved?

  4. #4
    Registered User
    Join Date
    08-22-2021
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2016 professional
    Posts
    7

    Re: Formula to add specific amount of years to a date

    I Use =DATE(YEAR(H6)+M6,MONTH(H6),DAY(H6)) for this work. In your case you would use A1 instead of H6 as the location of your date and M6 would need to be a field on that line where you enter the number 65 so =DATE(YEAR(A1)+C3,MONTH(A1),DAY(A1)) would work (you would type the number 65 into the field C3)
    Capture.JPG

  5. #5
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Formula to add specific amount of years to a date

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Row row row your boat
    Gently down the stream

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to add specific amount of years to a date

    anemae and Metoo7 thanks for the input, perhaps you missed that this thread is over 16 years old?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Formula to add specific amount of years to a date

    Thank you for your reminder, I will check the posting date in the future.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to add specific amount of years to a date

    Quote Originally Posted by Metoo7 View Post
    Thank you for your reminder, I will check the posting date in the future.
    No problem, I have done the same thing myself

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1