New Contributor

# How to avoid negative number of days

When using DATEDIF , i am getting negative number of days . For example instead of showing 8 months and 29 days , its showing 9 months and -2 days. How to make it show only in positive number of days.

 31/5/1988 1/3/2022 33 years 9 months -2 days

i want the result as 33 years, 8 months, 29 days

2 Replies

# Re: How to avoid negative number of days

@Viswa88 From the Microsoft support site:

The function just isn't always correct!

# Re: How to avoid negative number of days

As @Riny_van_Eekelen has already told you, the DATEDIF function isn't really supported by Microsoft except as a carry-over. I went to another website I often find helpful, and it made the same point more colorfully.

Here's a link if you wish to explore further: https://exceljet.net/excel-functions/excel-datedif-function

But your question is intriguing. I can envision a solution that could take advantage of some newer features of Excel (I have the LET function in mind). It would take some elaborate (??) conditional thinking,

• IF the number of days as in your formula is negative,
• then reduce the month by one,
• then find out how may days that month normally has,
• and if it's a February, determine whether it's a Leap Year,
• and then calculate which day it is by subtracting the negative day number from the appropriate .... (you get the idea)

Before I spend some time, which I might do just for the sake of satisfying my intellectual curiosity, let me ask you what version of Excel you are working with.