Excel Magic Trick #151: DATEDIF function (between two dates)

Calculate the days, months or years between two dates. See how to use the undocumented Excel DATEDIF function. See how to:
1) Calculate the number of completed days between two dates d
2) Calculate the number of completed months between two dates m
3) Calculate the number of completed years between two dates y
4) Calculate the number of days after completed years yd
5) Calculate the number of months after completed years my
6) Calculate the number of months after completed years ym

Category:

Science & Technology

Tags:

• Excel
• undocumented
• DATEDIF
• function
• days
• months
• years
• dates
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[familycomputerclub]

Excellent video. DATEDIF sometimes doesn't give the correct day difference. For example '=datedif(A1,B1,"md") where A1 is 01/18/1950, B1 is 01/13/2012. Answer in Excel is: 139. Just a random test that I did. What could be the reason?

[ExcelIsFun]

@familycomputerclub: Yes, because of the nature of dates, there will always be some trouble based on what assumptions you use. There are voluminous amounts written about this. If you search the Mr Excel Message Board for "DATEDIF" and username "barry houdini", you will find the most comprehensive discussion about the problems and what assumptions people use and what solutions they get.

[generous2100]

mate its showing me Number Error like dis #NUM! any idea ??

[ExcelIsFun]

I do not. Try posting your question and formula input numbers to THE best Excel question site:

mrexcel[dot]com/forum

[HarisOmanovic]

@ExcelIsFun Datedif stands for date difference. Not dated if.

Great videos though!

[ExcelIsFun]

@HarisOmanovic , ya I know, I just always say it the wrong way!

[ExcelIsFun]

Yes, because of the nature of dates, there will always be some trouble based on what assumptions you use. There are voluminous amounts written about this. If you search the Mr Excel Message Board for "DATEDIF" and username "barry houdini", you will find the most comprehensive discussion about the problems and what assumptions people use and what solutions they get.

[moazzamca1]

Hi Mike. I am having a problem with DATEDIF function. In some cases it does not calculate correct number of days. For example, I want to work out number of days after completed years (the "YD" argument) between 3-Nov-03 to 25-Feb-17. The system is returning 82 days while this should have been 115 days (28 days for Nov, 31 days for Dec, 31 days for Jan and 25 days for Feb). I can't understand why is this so. Please help.

[cbarlow3]

@dnorton4444

Well, this is a year later, but I think the DATEDIF is correct, dnorton, unless you count time intervals the way the Romans used to. It counts the interval from 1/1 to 1/3 as TWO days, for example, which is what most people expect. So in your second example, since 2008 actually has 366 days (because of the leap year), the interval from the first day of the year to the last day of the year is only 365 days (that year). The duration from 1/1/08 to 1/1/08 was the 366 days expected.

[ExcelIsFun]

You are welcome!