How to avoid negative number of days

New Contributor

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/19881/3/202233 years 9 months -2 days

 

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

2 Replies

@Viswa88 From the Microsoft support site:

Riny_van_Eekelen_0-1648816835550.png

The function just isn't always correct!

 

@Viswa88 

 

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.

mathetes_0-1648817868468.png

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.