CODEWITHSUNDEEP
exceldatedatediffdate-difference
MCM
MCM

Reputation: 1511

Wrong number of days Excel

I am trying to calculate the difference between days but receiving the wrong number of total days for that year. For the dates 2016-01-01 and 2016-12-31 I receive 365 days but not 366. I used the following function where I receive the same output:

=DAYS("2016-12-31","2016-1-1")
=DATEDIF("2016-1-1","2016-12-31","d")
="2016-12-31"-"2016-1-1"

I am not sure what I am doing wrong? Soemhow the leap year's are not properly accounted for.

Upvotes: 0

Views: 2649

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

I think you are misinterpreting the output from the DATEDIF function. Consider the following function call:

=DATEDIF("2016-12-30", "2016-12-31", "d")

This returns 1, which is the number of days between the two dates. Similarly, the following call returns 365:

=DATEDIF("2016-01-01", "2016-12-31", "d")

A value of 365 means that there are 365 days between the first day of the year (day #1, January 1), and the 366 day of the year (day #366, December 31).

As a sanity check, run the following:

=DATEDIF("2015-01-01", "2015-12-31", "d")

This returns 364 days, because 2015 was not a leap year and had no February 29 in its calendar.

Note that the end date must always be greater than the start date, otherwise you will get the #NUM! error. The call you made in your question will not even work.

Upvotes: 4

Related Questions