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

1. ## 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. ## Re: Datedif Negative Issues and General Issues Adding/Subtracting/Rounding Months and Days

convert 13 y 3m - 1y 6m in Months= 141
assuming d23 = 141

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

for months
Formula:
`Please Login or Register  to view this content.`

3. ## 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"

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

find the attached file

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