Download Excel Start File:
Download Excel Finished File:
Full Page With All File Links:

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


Xem thêm bài viết khác:


  1. i have this formula =K2+14-TODAY()&" Days" that serves as an overdue clock as soon as i enter a past or current date in cell K2 which is 1/31/2017, the value on the formula today is =Day 1073, tomorrow will be =Day 1074, and so on, my question is, what can i do to stop the count of day? the overdue clock is 14 days. how do i stop it as soon as it hits 14days or as soon as i enter a date in a separate cell?

  2. hai,
    This formula is showing wrong days in 2020 year.
    14/05/2007 09/01/2020 12 151 4623 139 7 353 151 months & 139 days 12 years & 353 days
    how to rectify this error.

  3. I guess the name of the function is DATE DIF (as in Date Difference) rather than DATED IF (as pronounced by the author).


  5. I have a unique issue, I am dealing with billing periods and move in dates. So I know what GL code to apply. Example, tenant moves in on 1/24/2019 and a vendors billing cycle is 1/2/19 to 1/31/19. This actually would have 2 different GL codes so how do I calculate that with an if statement. I am assuming it's going to be a nested statement of some kind – yikes! Can you advise?

  6. @ExcellsFun Hello Sir. thanks for sharing this. is it necessary to round off the dates when using for calculations? ex. there are 23.670098 quarters in this given start to the end date. do I use 23 Q or round to 24Q? Since your entry here are rounded off. Sorry if you think my question is not related to what you're sharing here. As I am using manual calculation on the dates to get the correct answer. Thank.

  7. Can I use this with a custom date? For example, I've got Month-Year as in "Jan 2019" for both my start and stop (I don't need days). I can't get the datedif command to work when trying to use these. I then formatted them as dates (even though I can't find one that meets my needs, but I was just trying to see if this worked) – no luck. Thanks!

  8. Dear Sir DATEDIF HOW TO ENABLE months and days,years and days in single is showing #NUM! ,please reply

  9. Hi can anyone give me the formal if the end date is blank and i need to check the difference as of today day
    Thanks in advance

  10. Wonderful video… Could you please share how I can get the exact years and months written out as text? Thanks so much!!

  11. When i use this function, sometime it shows calculations like 57 Years, 11 Months & 30 Days instead of 58 Years. Can anybody let me know what i've to do?

  12. Very cool. Worked perfectly. Thank you. Could the function be called "DateDif" as in the difference between the two dates and not "Dated If" like an "IF, THEN" statement?

  13. Thank you for this. I have a new found need to do all kinds of work in excel and have never had to do formulas before. This was a life saver and your instructions were very easy to follow.

  14. hi Nice video I have a query regarding this function I need the age in years if the child age is more than 1 years i need result in years only but if the child age is less then of one years then i need results in months hope you understant my problem what i want to say. solve the my problem please

  15. thanks .. your videos are full full of various different methods .. which are very interesting and educative ..hope a nice complete tutorial on VBA ..

  16. Excellent explanation ExcellsFun – thank you.

    I copied/pasted this formula from, Tal Ekboy, farther down Comments List for y'all to see. This ONLY worked for me (2007 version) when I changed the format of the dates to appear either as 5/1/98 or 05/01/1998, or 1-May-98 (didn't work as I had it May 1, 1998).

    Look closely for the few spaces, hard to see, and the one dash. Whew took me a while to figure out why this wasn't working for me too. Of course, below formula assuming your dates are in D and E — change it. Man this is one long string ….

    =DATEDIF(D16,E16,"y")&" "&"Years"&" "&DATEDIF(D16,E16,"m")-(DATEDIF(D16,E16,"y")*12)&" "&"Months"&" "&"and"&" "&DATEDIF(D16,E16,"md")&" "&"Days"

  17. I need your help. I want to learn how to count how many weeks between date range. if u have any video pls Shae the link. thanks

  18. Can you show how and why things work like how the Y column seems to be connected to everything in its row? Feels like this lacks context and I'm not about to watch 150 videos to try and figure this out.

  19. If I use "MD" unit for DATEDIF function as the following example =DATEDIF(DATE(2015,3,14),DATE(2017,7,13),"MD"), it returns 29 as a result. Is it a correct result? If it correct, could you please explain me? Thank you in advanced. I am looking forward to hearing from you soon.


Please enter your comment!
Please enter your name here