0:00 All right. I wanted to share this with you because I think, one, I found something very useful, but then also I found something very distressing and very weird about sort of our life and when life might end here on Earth.
0:14 I know that sounds crazy, but here's what, here's what we are gonna do. You're gonna try to figure out how many days between due two dates.
0:21 Now, how do you do that? You might do some math because dates are numbers, right? They're, they're the number of days since 1900.
0:30 So even though Google Sheets displays the number three slash 12 slash 2023, like today's March, th March 12th 2023 and we sh show it like that, right?
0:42 Three 12 or or 12 three if you're in different time different country, right? What that number actually is. So if we take this number and we go equals A three and we change the formatting to just a number, this is 44,997.
0:59 That is supposedly the number of days January 1st, 1900. And so what's really cool, and I have some other videos about this, is you can absolutely do math on dates and say, okay, between these two dates, just this one minus this one.
1:11 And that's the difference. But there's this also, there's also another formula called dated If now dated if does not actually say what this does, what this actually does is it finds the difference between the two dates and you can then change the unit.
1:28 So this is really important because if you just do the math, you'll get days, but you can change this to months, you can change it to years.
1:35 Meaning you're not limited to just doing the math and then having to figure out, okay, if this is that many days, how many months is it?
1:42 Or how many years divided by 365 or months divided by 30.4, right? What you can do is you can say, okay, here's today's date and here's an end date.
1:51 Say, actually, we should probably change this like 2024. And there's 659 days between these two dates. And so what you can do is this is a great for like countdowns of, of things that are happening maybe in project management or task management or managing other people, and you're like, Hey,
2:07 I have this due date for you on this day and here's another day. This is today how many days are left?
2:13 But for this, you can do this. For campaigns, you can do, this is great. And how we do this is we go equals dated if dated.
2:21 That's a with a date, D if date difference actually. So I just realized that actually dated, it's not dated if it's date diff, oh my God, that's probably why it's very hard to figure out what this does, cuz I kept saying dated if, but it's date diff, okay?
2:38 Start date is going to be let's say today and end date is gonna be this other date, and we're going to change the, the unit we want to measure by is a capital D days.
2:50 So that's how many days are between those two. This is interesting. Right Now we can say, okay, that's how many days are left.
2:58 But Here's something else that I figured out. I was like, I wanted to try to see like, is it true that N 1 1 1900 is the like first day?
3:12 So I changed this to 1 1 1900. First I get a number error here. Should be honor before dated. So, okay, I actually just have to flip these.
3:26 So it's just B3 then A three. Okay, that's 4 4 95. That's a, that's how many days since January 1st, 1900. But if we look at this again, go equal here.
3:36 This is 44,997 close, right? But I thought literally that the 1900 date system was the number of days since that date, maybe including that day.
3:50 So maybe one difference, but why is it two? So let's do this equals b3, okay? January 1st, 1900 is actually two. The number two, that means it's the second day.
4:03 So what's the first day? So is the first day, honestly, like it's not Day number one is December 31st, 1899. So day zero is 1230, so December 30th, 1899.
4:22 So even though we're using the 1900 date system, the days the day zero, the start date of this system is not January 1st, 1900, but two days prior.
4:36 This is very interesting when I went and looked at date as well, so not just date diff, but date, there is this little thing, this little bullet point here, and it talks about Google Sheets uses the 1900 date system and it says the first date is January 1st, 1900, but that is number two.
4:57 So the first actual date is December 31st, 1899. This is number two date. And this is interesting is that as we keep going down for years, 1900 to 9, 9 99, Google Sheets will use that value as the year wait only until 9,999.
5:23 What happens after? And then it says here, for years less than zero or greater than 10,000 Google Sheets will return the number error.
5:33 So it will not handle any years before zero, which is not like BC <laugh>, it's, it's literally just 1900. Like any years before 1900 or no, no, sorry, not 1900.
5:52 Yeah, zero. This is zero BC I'm my mistake. But what is this greater than 10,000? What, what is the deal with Google Sheets that it's not going to work after 10,000 years?
6:04 Does this mean like we're in the year 20 2023? We only have, we have less than 8,000 years left for Google Sheets to exist.
6:15 Will they change this in the next eight, 8,000 years? I wonder, this is quite interesting and a little bit funny, sorry.
6:26 But yeah, I thought this date diff now, now I realize date diff is a really cool function. We can also see the change here instead of d I put this unit in quotes, let's change it to Y and we now know how many years are between these dates.
6:42 That's pretty cool. We can even do months with an M. That's really awesome. See how many months maybe you're like financial quarters are happening, you want to be like, oh, I want month divide by three to find out how many quarters, right?
6:57 But there's also yd, I think YD means years, decades, Nope. Well, this is much better to actually get the answer from the documentation here on Google, but it says MD is the number of days between starting an end date after subtracting whole months.
7:18 Then YM is a whole number, number of whole months after subtracting whole years. And then the number of days between start date and y end date is in Yd is only the days assuming that it's no more than a year apart.
7:36 So this is very interesting. These are three very interesting ways to use date diff here <laugh>. And it even gives us some examples of what questions you might have.
7:47 So after subtracting whole years and whole months from my age, how many days old am I? Strange question. If you want to use unit ym after subtracting whole years from my age, how many whole months old am I again, very strange question.
8:04 How many days has it been since my last birthday given my birthdate and today's date? Alright, very interesting little example questions here.
8:13 So this has been a bit of fun date trivia and a new date related formula this dated if date. If I keep saying dated, if date it, date if to give you a little handy tip whenever you're trying to do countdowns to say campaigns or certain tasks need to be done, or even what <laugh> might
8:35 be useful is how many days past a certain date they are. If you have a deadline and you're past that, you can say, Hey, it's been three days since this deadline.
8:43 Very great to add this. If you're doing email notifications of upcoming events, can, this doesn't, this means you don't have to do the math.
8:56 You can just do this date diff and it'll tell you how many days do we have left or how many days has it been since.
9:03 Really cool stuff. I really hope this has been useful.