 If you're working in a business or organizational setting, there's a good chance that a fair amount of the data you're going to have to work with involves dates. Now, it turns out that dates work a little differently. They're kind of special in spreadsheets like Google Sheets. And there are also special functions for dealing with dates that actually can solve some practical problems. And so I want to take a couple of minutes and walk you through how you can enter dates, format dates, and then do some operations on dates that might be helpful in your particular line of work. The first is that entering a date, you can just enter it as five slash 10 slash 18, and it will automatically fill in with the year if it's a date in the near future. Now, this format is the default, at least in America, it's the month slash day slash year, that's the American approach. But maybe you want to do it differently. So for instance, if you're familiar with drunken white, they talk about doing day, month, year, actually, how I prefer to do it. And the way we can change that without changing the underlying data is to come up to format, to number, and then come way down here, you see we have a few date formats, but I'm gonna click on more formats, and then more date and time format. So we're going kind of deep. But this is it right here. So if I click on that and hit apply, we have the day the 10th, the month written out in letters may and then the year. And drunken white recommends this because actually, it's a nice way of separating the values, you don't have to use commas, and it's very clear that what's the month and what's the day. On the other hand, there's a lot of situations in which the ISO format for dates, that's a international standard is going to be really, really helpful. And for no other reason, then it sorts correctly. And what this is is year dash month dash day. And we can get that one again by going to format, to number. And then we come on down to more formats. And we can scroll down here. This is the ISO format year month day. And again, if you're entering data, that will always sort by date correctly. So I use it in the number of settings. And it's a very common in computer programming. And then the last one is if you want to totally avoid ambiguity, just write it all out. And we can do the long version. I'll come up to format number. And I'll come down here to more formats. And I'll choose this one, it writes out the full name of the day, the month, the day, common, the year. And it's kind of long, but it's very easy to tell what's happening. And so I just call that the long version. But now let's talk about how to do some math. One of the interesting things is, you know, even though this is all written out really long, you see it's still coded this way. If we double click on it, you can see the date user and we've got this right here. So that's quick and easy and hit escape. Now, if you want to find out what five days from today, it's really easy to just do math. All you got to do is select the date, which even though it's formatted this way, if you click on it, you see it's underlying is represented this way, you also have a date clicker here. So you can select stuff. All you need to do is a formula that says take that and add five. Now I'm going to paste that formula right here. And you can see it's the 15th. But look, if I click on it, you can see the formula, simply take that cell, add five. And it keeps the same format. And it's the date. Now, what if you want to add five working days, because often that's more important when you're working in a professional setting. That's a little hard to set up on your own, but there are special formulas for that. So for instance, you can use this formula, which is work day, and then you specify the cell you want to start with and then how many working days. And it gets you to the 17th one week later. Or what if you want to add five months? There's another one for that. It's a date. This is where you're adding months. And it goes to that cell B10 adds five months, you know, fortunately, that's pretty easy to know five months from May is October. Now, let's look at some other formulas for calculating differences between dates. So for instance, right here, we want to find the difference between this date and this date May 10 to July 14, because we're getting ready for best deal day. One way to do that is to simply do subtraction, take the later date, subtract the first one. Again, basic arithmetic. And what we have is 65 days from those two dates. But there are other ways to do this. So for instance, there is the special formula days, where you put the end date, the beginning date, and it gets you the exact same thing. I mean, you could just write subtraction and get what you want. Or if you want working days between those two days, you can use this one net work days. So the number of work days between those two, that's a nice one. Previously, when you use work days, it was to add a certain number and see what it was in the future. Now we're doing subtraction. And then if you want to see the number of months, whole months ignoring the extra days, you can use this function, which is date diff, where you give the first date. Now notice the order here is a little different because you do the first date, the second date. And then you specify this argument m, which stands for months. And you put that in quotes. Because there's another variation. If you want to know aside from those two months, how many days are there, you can use the same formula. But you it's still date diff, and you give the first date the second date. But now you specify MD, which gives you the number of days after excluding the number of months, kind of an obscure one, but it's there if you need it. Also, if you want to see how long an event lasts, that is elapsed days, there's a few ways you can do this. Let me scroll up a little bit here. The easiest is suggest you arithmetic. So for instance, right here, all we're doing is we're taking the end date, we're subtracting the beginning date, and then we're adding one, the reason you need to add one is because if the end date and the start date were the same, it still means it lasts one day. So any number minus itself is zero. So you add one to the total number of days. Another way to do it is with this function, the days function, and you just have to add one onto it at the end. Now if you start on a working day, that is Monday through Friday here in the US, there is a special function that you can use. Again, it gets back to net work days, but you still have to add one on to it. If you start on a weekend, it doesn't work quite right. But that will often help. There are also these two current day and time functions. So for instance, we have one right here called now I'm just going to type that in. Now, you can see I'm recording this on May 18. Or we can do it today. May 18. Now gives you the time up to the seconds. And so those might be helpful. And then finally, elapsed time, how much time passes between two things, we've done dates, but dealing with minutes and hours is kind of a special thing. So the first thing I want to do is if you just do subtraction, where you take the end date and time and you subtract the beginning date and time, by the way, notice you have to use 24 hour time for this to work. So this actually means 315 in the afternoon. If you do that, you get 0.73 and you might what on earth that well, that means 0.73 or 73% of a day. If you want to turn that into hours, then you have to do a little bit of formatting on it. You simply take that and you multiply it times 24, because there's 24 hours a day. And that means 17.5 hours. On the other hand, a lot of time people don't like fractional hours, they want minutes. And so the way you can adapt that one is by throwing in some extra formatting. This one is a little tricky. It means you have to take that number, but you have to then format it as text. And then when you do that, you have to tell it specifically the format that you want. And so we put the comma and then in quotations, we put the H with the H in square brackets to indicate it's elapsed time. And then the colon and the minutes, it's, it's a little cumbersome. But once you get it, you can just copy it and use it all again in the future. And that means 17 hours and 30 minutes have passed from this day and time to this day and time. Finally, this is actually a formula that I use a lot when I'm planning my videos, because I need to add up how long things take. And if you want to do the kind of math that's involved, you do a small variation on this. Let's say I'm going to do 15 videos at six minutes each. Now, you can just multiply in your head 15 times six is going to get us 90. So we know that's an hour and a half. But you can get a lot of other values, if you want the spreadsheet to do it for you, you have to do a slightly complicated thing. You first have to do the two things you're multiplying the 15 and the six so 15 videos at six minutes each. But then you have to divide it by the number of minutes in a day. And you get that by 24 hours times 60 minutes. I know it's it's also 1440. It's really kind of a weird thing. And then you tell it to format it as hours and minutes. And that's within the text command. And that'll tell you that this is one hour and 30 minutes total. Again, that's a function that I use regularly. It took me a long time to figure that one out. But it's a way of working with time elapsed time, days in the future, time between two dates, and all of these will give you some of the insight that actually it's really hard to do by hand. But once you get the right formulas, it can make it a very quick and painless process in Google Sheets.