Temporal Functions

Quick reference

• 00:04 I'm super excited to be able to show you this function because honestly EOMONTH is
• 00:08 actually one of my all-time favorite functions in Excel.
• 00:11 And I know that sounds weird, but I'm an accountant, and
• 00:14 everything about my life always revolves around month-end.
• 00:17 So what EOMONTH allows me to do is it allows me to take a date and
• 00:21 convert it into the month and day and that is so important to my work.
• 00:25 So here's how this works.
• 00:27 I'm going to to say =, going to type in EO that's enough to get to EOMONTH.
• 00:31 I'm just going to press tab to commit it.
• 00:33 We're going to grab a start date, and
• 00:35 then I'm going to ask it to give me a month-end zero months from now.
• 00:39 Why would I do that?
• 00:41 Well, because February 5th, I want the month end, which is February 28, 1982.
• 00:47 And the important thing that you'll realize here is if you go back and
• 00:50 check your calendar for 1982, there were indeed only 28 days in February.
• 00:55 All right, so that's cool.
• 00:56 Now, let's take a look at this next one, though, we got February 29, 2008.
• 01:01 Let's go and flex that date by 12 months to get the month-end.
• 01:04 And what we get is we get February 28, 2009.
• 01:09 And this is important to realize because that is the right date.
• 01:12 There are 29 days in February 2008.
• 01:14 It's impossible for there to be 29 days in
• 01:17 February 2009 because there's a leap year component that goes on here.
• 01:22 This is not giving us March 1st, because it's asking for the EOMONTH.
• 01:25 It is giving us the February 28, which is perfect.
• 01:30 Likewise, if I go and roll all these things down here you can see that if we
• 01:34 go for the month-end 12 months after December 1st, 1959, of course,
• 01:38 we'll get to December 31st, but 12 months out should be 1960.
• 01:42 And that's what we get.
• 01:44 December 20th, 36 months out, that's three years.
• 01:48 Should give us December 31st, but instead of 1950, we'll have 1953.
• 01:53 And you can see this is working nicely.
• 01:55 August, four months, well that's September, October, November.
• 01:58 That is December, that is the fourth month.
• 02:00 And that works out quite nicely there.
• 02:02 And if we look at November here we're going three months, well,
• 02:05 that's going to be December, January, and February.
• 02:07 And the nice thing about this one, again, it is February 28th,
• 02:11 29th aware, and returns is the correct date here.
• 02:14 But the cool thing about this function is that it doesn't just accept zero or
• 02:18 positive numbers.
• 02:19 It also accepts negative values.
• 02:21 So when we take a look at January 3rd, 2014, and we say, give me minus 1 month,
• 02:27 and give me the month end, it's going to take that January 3rd date,
• 02:31 back it up by a month, which is December 3rd, and give us the month-end,
• 02:35 which is naturally December 31st of the previous year.
• 02:39 That is great.
• 02:40 want to go 12 months back from February 28, sorry, February 29th, 2008.
• 02:45 What do we get?
• 02:45 February 28th, 2007.
• 02:48 Awesome, awesome feature.
• 02:50 Now, what is EDATE?
• 02:52 Well, EDATE is actually very similar to EOMONTH.
• 02:55 We provide it with a date, we say how many months different would you like me
• 03:00 to shift that date and return the same day, x months later?
• 03:04 Now, in this case, I'm returning the exact same day because I've shifted zero months.
• 03:09 Why would I ever do that?
• 03:09 I probably wouldn't.
• 03:11 But you can see how it compares when we actually roll
• 03:15 it down into the other values.
• 03:17 In this case here, February 29th, we're shifting it by an entire month,
• 03:20 we get February 28th, the same as we would get for an EOMONTH function.
• 03:24 But when you look at December 1st, 1959, and we shift it 12 months, unlike
• 03:28 EOMONTH because it's the end of the month this one now gives us December 1st, 1960.
• 03:34 Notice, August 14th gives us December 14th, that's four months later.
• 03:38 This is not 365 days.
• 03:41 It is leap year aware, which is great because you can actually
• 03:46 get the same day next year or the same day next month.
• 03:50 In addition, just like EOMONTH, you can also back things up.
• 03:54 So you'll notice here that we've actually shifted this back one month from January
• 03:58 3rd, we get to December 3rd, and from February 29th, we get to February 28th.

Lesson notes are only available for subscribers.