Hi, this is Wayne again with a topic “Date Functions and Formulas in Excel”.
In this article, i will show you several date functions and formulas in microsoft excel, and i think you’ll find them to be very useful. And in this first example, i’d like to just quickly show you how to find the number of days or years between one date and a second date. So here we have a customer relationship tracker. It shows a list of customers and the date that they began as a customer of this company, and let’s say this company’s 25 year anniversary is coming up and for that anniversary i would like to know and be able to celebrate how long each customer has been A customer of the company to do this is pretty simple. Really. All i have to do is click in this case in cell f3 type equals and then i’m just going to click on the anniversary date, 12, 20, 21 and then i’ll type, minus and then i’ll click. The begin date for this customer then i’ll just tap, enter on the keyboard, and that is the number of days between the begin date and the end date or the anniversary that we’re going to be celebrating now the number of days may or may not be useful.
So i’m going to click again on f3 and i’ll go up here to the formula bar and i’m going to put the e3 minus d3 in parentheses and then divide that by 365 tap enter on the keyboard. And that tells me how many years gina pullen has been a customer. I can just double click on the green square in the lower right corner of the cell, and it will copy that formula down the spreadsheet so that quickly, i can know how long each customer has been a customer of the company.
Now there are other ways to do the same thing i didn’t have to include the anniversary here in the spreadsheet, for example, but the key takeaway from this first example should be that you can subtract one date from another date to get the number of days between Them and then just divide it by 12, divided by 365 or whatever you want to do to find out how many years or months or days, they’ve been customers. Next, let’s look at how i could use some date related functions to pull out information from this date. Column here in d, so let’s first get the day, i’m just going to start by typing equals and then i’ll type day left parenthesis and then i’m just going to click on the date that i’m trying to pull the day out of so d3 tap enter on The keyboard and i’ve pulled out just the day number out of this date.
I can click on g3 double click on the green square and it copies down the sheet. Let’S try pulling out the month, i’ll, just tap, equals i’ll type month. Left parenthesis click on the date in question.
I should put in a right parenthesis, but i don’t have to i tap enter and it pulls out the number four just double click on the green square and it copies down the spreadsheet. Let’S try year. I bet you can guess what this is equals year left parenthesis, click on the date tap enter and then i click on the cell. Double click on the green square. Now, what? If you don’t want to pull out the number of the day of the month of the year, but rather you want to pull out the text version of the date.
Let’S try that here in j3 i just click i’ll type equals and then i’ll type. The word text with a left parenthesis i’ll click, the date in question and then i’ll put in a comma and in quotation marks. I’M going to put four d’s representing day. I need to close the quote, and i should put in a right parenthesis: tap enter and excel has pulled out the day as tuesday now think about how cool that is excel knows that april 23rd 2002 was a tuesday, and it’s able to extract that data. Out of this date, because i used this formula, i’m going to double click on the green square, to copy that information down the page and let’s do the same thing, but this time with month. So i type equals text left parenthesis and i click on the date.
In question, comma, in quotation marks instead of four d’s i’ll put four m’s close quote right: parenthesis tap enter and i’ve extracted the word april from this state i’ll double click on the green square, to copy it down the spreadsheet, and now, let’s do year equals text Left parenthesis, click on the date quotation marks for whys for year, close quote right, parenthesis tap enter and then i can click on the cell. Double click on the green square, which is the fill, handle or auto fill handle and it copies the data down. So this is a really neat way to work with dates in excel and it’s fun to think that all of this data is embedded just in this simple date here and each of these pieces of information can be extracted from it using the formulas and functions that I’Ve shared with you thanks for watching. I hope you found this video to be helpful.
If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you could support me through my patreon account or by buying channel merch and you’ll, find information about those options. In the description below the video .
