+ Reply to Thread
Results 1 to 4 of 4

Datedif Negative Issues and General Issues Adding/Subtracting/Rounding Months and Days

  1. #1
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Datedif Negative Issues and General Issues Adding/Subtracting/Rounding Months and Days

    Hi all,

    I've got a couple of general issues adding/subtracting years/months/days and an issue using Datedif and I'm wondering if anybody would have a solution?

    I've attached a sample worksheet showing the issues I'm having.

    Datedif Issue

    When adding/subtracting dates using datedif I occasionally get a negative value, such as subtracting 1 years 6 months 4 days from 13 years 3 months 27 days with the result being 12 years -3 months 23 days!

    Is there any way to prevent the negaive from occuring (and in this case have excel display the correct answer of 11 years x months x days) ?

    Sum Months and Dates

    I need to sum a month and days figure and have the result rounded to the nearest month, at the moment I have a figure of -3 months (due to the issue above) 23 days and I need to round the month and days figure to the nearest number of months (4 in this case) in cell D17.

    Subtracting Years / Months / Days

    Lastly I need to subtract Years and Months from Years and Months, however the cells are formatted as text and contain text as well as numerical values, so I'm unsure how to handle this.
    DateDif Issues.xlsx
    Any help offered would be appreciated.

    Thanks

    Dave

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Datedif Negative Issues and General Issues Adding/Subtracting/Rounding Months and Days

    for your first problem
    convert 13 y 3m - 1y 6m in Months= 141
    assuming d23 = 141

    then for years divide by 12
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    for months
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Datedif Negative Issues and General Issues Adding/Subtracting/Rounding Months and Days

    hmmm alot of the coding is to get around the "YEARS" and "MONTHS"
    thought about using custom number formats instead?
    Attached Files Attached Files

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Datedif Negative Issues and General Issues Adding/Subtracting/Rounding Months and Days

    find the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ 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. Issues with rounding
    By kasperblue in forum Excel General
    Replies: 2
    Last Post: 08-04-2012, 05:41 PM
  2. Issues with formula when adding/subtracting rows
    By hektisk in forum Excel General
    Replies: 1
    Last Post: 07-21-2011, 11:16 AM
  3. rounding issues
    By emt3000gt in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-06-2008, 07:07 PM
  4. [SOLVED] rounding issues
    By [email protected] in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-27-2006, 05:20 PM
  5. Rounding Issues
    By Louis in forum Excel General
    Replies: 1
    Last Post: 02-04-2005, 08:06 PM

Tags for this Thread

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