Hi, this is Wayne again with a topic “Using the Top Secret DATEDIF Function in Excel”.
In this Excel video, I’m going to show you how to use the date diff function to show the difference between two dates. This is a little known function, but it’s powerful and it’s very useful. So here I have a spreadsheet that tracks some former customers of a company and also some current customers, and I would like to be able to track how many days or months or years, a particular customer either was or is a customer or client of the company And we’re going to use the date diff function to do so so, let’s click on F2 and I’ll just type in equals date. Diff now before I type in diff notice that we’re used to when we type in a function we’re used to getting some tips. Some hints from Excel but watch what happens in this case date diff and I get nothing.
It’S almost like Microsoft wants to keep this function a secret. Once I put in the left parenthesis, you can see that Excel does recognize this as a function, but there are no tips, no help. I just have to know how to use it. Fortunately, it’s pretty easy to use. What I’m going to do is I’m going to select the begin date, the first date and then put in a comma, then I’ll, select the second date and I’ll put in a comma.
And finally, I need to tell Excel how I want the difference between those two dates to be counted. Do I want to count it based on days, months or years, and you can see here at the right – there are some hints to help. You know which one to put in so, let’s start with days I’ll, just type quote marks D, close quote and then I should put in my right parenthesis tap enter on the keyboard and now I know that Gina was a client of this company for 7455 days.
Now I can click back on F3 in this case, and then I could double click. The autofill handle the little green Square in the lower right corner of the cell. When I do that the formula is extended all the way down the spreadsheet and look.
It looks like an error has been identified, there’s no way that Nancy began being a client after she stopped being a client. So clearly this was supposed to be 2019 I’ll change that tap enter and the number is updated. So that’s one of the powerful benefits of using the date diff function or many other functions that if you reference other data in your spreadsheet and that data changes, the results of the formula are updated dynamically instantly.
Now, let’s do the same thing but produce months. So I’ll type equals date, diff left parenthesis, click on my first date or I could just type the cell reference D3, comma, E3, comma and then in quotation marks, I’ll put M right, parenthesis tap enter and I can click there. Double click on the autofill handle to apply it down.
You can probably guess how to do the same thing for years. Equals date. Diff left parenthesis.
First date, comma end date, comma, in quotation, marks I’ll put a y, close quote: tap enter, select H3, three double click on the autofill handle, so each of these numbers really represents the same amount of time. It’S just. This is counting just years. This is counting just months, and this is counting just days now in this example, I’m examining two dates that are both in the past. What if I would like to compare a past date to a current date, so I’ve switched over here to the current customers spreadsheet and now I would like to calculate the number of days, months and years between when each of these people became a customer and today’s Date, how would I do that? Let’S click on E3 I’ll type equals date, diff left parenthesis, I’ll, select the date in the past, comma and then I’ll just type. The word today and then left parenthesis and right parenthesis I’ll put in a comma and then in quotes I’ll just put a d and my right parenthesis tap, enter and there’s the answer.
I can select E3, which looks like it has the number 796 in it it, but really it’s got this formula so when I double click on the autofill handle it copies and applies that formula all the way down the spreadsheet. Let’S do the same thing for months equals date. Diff left parenthesis the date in the past comma today left and right, parenthesis, comma and then m in quotation marks right parenthesis tap enter, I select F3 double click on the autofill handle and the formula is applied. All the way down, one more time equals date, diff left parenthesis. The date in the past comma today left parenthesis right, parenthesis, comma, in quotes y for years, close quote right. Parenthesis tap enter, select G3 double click on the autofill handle, so as you use Excel, I’m sure you’ll see examples and times when you’ll want to use the date. Diff function, it’s kind of a little known, hidden function, but very useful in many situations, specifically when your spreadsheet tracks dates. If you’d like to learn more about the today function, please watch one of my other tutorials, specifically on that fun function. Thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow And subscribe, and when you do click the bell and you’ll be notified. When I post another video, if you’d like to support my channel, you can do that by clicking the thanks button below the video or you can support me through my patreon account or by buying Channel merch and you’ll, see information about those options in the description below The video speaking of patreon I’d like to give a quick shout out to my five dollar patreon supporters. Thank you so much for all you do for the channel. I really appreciate it. Some of you have been with me almost from the beginning, and I just really appreciate all of your support.
.