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

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



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

Nguồn: https://thegioiinuv.com/

Xem thêm bài viết khác: https://thegioiinuv.com/tong-hop/

Related Post

32 Comments

  1. G

  2. ع

  3. what about weeks?

  4. dude you just saved my job

  5. 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? jds74@hotmail.com

  6. 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.

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

  8. Where can I find how to do this with the example's columns P and Q?

  9. does it work right if the date is in mm/dd/yyyy format?

  10. by the way Im pretty sure its 'date dif' not dated if..hahaa

  11. I CAN'T PUT THE FORMULAAAAA

  12. I NEED FORMULA FOR IF 30 DAYS IT SHOULD BE COUNT AS 1 MONTH , IF IT IS 46 DAYS I MEAN MORE THAN ONE AND OFF MONTH IT SHOULD BE CALCULATE AS 2 MONTH. CAN YOU

  13. 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?

  14. @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.

  15. Thank you..👌

  16. 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!

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

  18. garrulous

  19. 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

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

  21. Hahaha!!! You inspire me every day. This was very difficult for me. Thanks for being there for everyone.

  22. I get the #NUM! error. I was wondering, can this be applied in MS OFFICE 2013?

  23. It's not Dated IF. It's Date DIF – the difference between two dates.

  24. thanku

  25. I'm in trouble with my table. I want a solution. How can I contact you

  26. DatedIf Formula [MUST WATCH]

    Link To Follow : – https://youtu.be/WC19cqhVdTE

  27. I tried today on excel 2010 version and it shows #value! Why it's like this can somebody explain.

  28. its gud

  29. 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?

  30. Great video help me alot

  31. 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?

  32. 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.

Leave a Reply