+ Reply to Thread
Results 1 to 3 of 3

Formula to show a specific date instead of just adding a number of years

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

    Formula to show a specific date instead of just adding a number of years

    The standing formula I use is this =DATE(YEAR(H6)+M6,MONTH(H6),DAY(H6)) which takes the date in field H6, adds the number in field M6 and displays a date in the future i.e 7/9/2018 plus 6 = 07/08/2024

    But in some instances I need to do is this instead:

    I need to take the date displayed (in A2) add one year and then for the date calculated to always be the 31 March. It's an audit date for finance purposes so in this instance the date 7/8/2018 needs to return a date of 31/03/2025. The formula =DATE(YEAR(A2)+1+E2,MONTH(03),DAY(31)) returns 31/01/2025 so I'm close. Where am I going wrong please?

    AuditDateFormulaError.JPG

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Formula to show a specific date instead of just adding a number of years

    Try
    =DATE(YEAR(A2)+1+E2,3,31)

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

    Re: Formula to show a specific date instead of just adding a number of years

    Thank you so much, yes that worked great.
    You've just saved me hours of work

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to add specific amount of years to a date
    By WinterTom in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-23-2021, 11:57 PM
  2. [SOLVED] Formula of number Years between two date
    By shiva_reshs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-22-2021, 05:17 PM
  3. formula needed to add a number of years to a date
    By Maryjoefernandez in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2013, 07:41 AM
  4. Formula for adding a date to a specific number
    By Riggs18 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-16-2013, 03:54 PM
  5. [SOLVED] Adding number of years selected from a ComboBox to the storage date
    By Help_Required in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2012, 06:41 AM
  6. [SOLVED] Need help w/formula to find to the next weekday (Tuesday) after 7 years from specific date
    By sfmauldin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-04-2012, 08:17 PM
  7. Replies: 9
    Last Post: 01-22-2009, 06:01 PM

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