here I'm going to show you how to get the difference between two dates broken out into years months and days and it's one formula that works like this as the time gets closer and the years are gone the formula updates as well so we don't have this nasty zero year zero months with a bunch of extra commas in there and even though I'm going through the whole formula here in the tutorial make sure to download the file for it and you can just copy and paste the resulting formula I'll put a link to that below this video as well as links to my online Excel courses but now let me reset the spreadsheet and show you how it's made what we're going to use here is the date diff function this guy right here which is Loosely documented but we're not going to get any help filling in the arguments and we're going to start by making the years then the months and then the days and then we're going to combine all of that into one formula down here then we're going to add a little bit extra to make it so that we don't see every element if that element has nothing to show for it so let's first start with the years equals uh date diff notice it doesn't fill in we input the start date and we input the next date you could input the today function if you wanted this to be automatically updating but we're going to use hard-coded dates for now and for the final argument you want to input one of these units which we're going to cover as we go along to get the difference in the years we input a y like that hit enter and we get two and that's really all there is we're going to use two more date div functions but the arguments can be a little bit confusing so now that we have the years let's go to the months we want equals date diff start end and now the argument so let's input M for complete months close it up hit enter and we get 25. and let's make this 2024 to make the math a little bit easier all right so we have one and we have 13 and that's correct there's 13 months between these two dates but we don't want them because we also want to show the years so essentially we're going to subtract 12 from this and you can do a bunch of math and make that work or we can use the other version here for getting months the description is confusing though I find it much easier just to play around with it like this to understand how it works so if we use M we get 13. if we use y m we get one so we can output one year and one month and finally all we need is the days so date diff hour start date our end date and what do we want let's go for D at first 405 total days between these two dates but that's not what we want we could try year a day and that'll give us 40 and it tells us the difference between these two dates as if they were in the same year but we want to go with MD that removes the years and the months from the calculation so the difference between 1 and 10 is nine and that allows us to make this formula down here one year one month nine days and all that we have to do is to combine these formulas this will give us a number one and we can go like this and type years enter and there we go and then the rest of this formula is just going to be combining the date diff along with some formatting options so first off we need a little space here to make it look a little better now let's go ahead and add a one more how about the months we're going to copy this guy right here and go down here put a comma and a space there go for an ampersand Alt Enter to go to the next line paste that guy in there just makes it a little bit easier to see then let's go for another Ampersand quote space and how about months close quote enter one years one months and that's how you put everything into one cell in one formula so first get the date formulas working how they should work then combine them with some formatting and your final guy can look something like this it is a little bit bigger but don't get overwhelmed all I did is follow the same pattern as I just showed you but then I built on that so here we have our regular date diff for the year and all I do here is I put it inside of an if statement and I say hey if that's greater than zero if you have something more than zero to return then go ahead return that value and return the years with it then we go to the next line and here's where I say hey if we have something for the year and something for the months then I want you to also return a comma and a space and we repeat that pattern for this entire formula so every line is a separate if statement is a separate element we have something that displays the years here something for the comma here for the months here for the comma here and for the days here and if you're in a newer version of excel you might want to throw a let around this guy nice interesting little function but here that's all there is and that's how you can get a nicely formatted years months and days text for the difference between two dates now if you'd like to see more of my tutorials make sure that you subscribe and hit the Bell icon and if you like this tutorial give it a thumbs up and check out some of my courses on teach excel.com I'll put a link to them below this video but for this tutorial that's all there is