Temporal Functions

Quick reference

• 00:04 In this video we're going to look at temporal functions and we're actually
• 00:08 gonna look at one of my all-time favorite functions in Excel which is EOMONTH.
• 00:13 Now, I'm an accountant and
• 00:14 everything in accounting is always about the end of the month.
• 00:17 We don't really care about the rest of the days.
• 00:19 It's always about when are we going to put our financial statements out.
• 00:22 We always do that on the last day.
• 00:24 So the challenge is, when somebody feeds me dates that look like these,
• 00:27 that are scattered throughout the months, I need to convert these into month and
• 00:31 dates.
• 00:32 Let's start here, with this one.
• 00:33 February 5th, 1982.
• 00:36 Well the challenge with this guy is that February could have 28 or 29 days.
• 00:41 So how do I make sure that I get a month end that's returning the correct
• 00:44 components?
• 00:45 And this is where the EOMONTH function comes in super, super useful.
• 00:49 The EO stands for End Of and if I go and
• 00:53 say =EOMONTH I can provide a starting date of February 5.
• 00:59 Comma, and then I can say, I'd like to see it for 0 months.
• 01:03 Now, that sounds a little weird, but what it does is it returns
• 01:07 the end of that specific month, and in this case, February 28, 1982.
• 01:11 Now this is super useful because it's February 28 29 aware, so I know by
• 01:18 feeding this into the Excel formula, that there's only 28 days in February in 1982.
• 01:23 That's perfect.
• 01:25 What if we go and take May 10, 1989?
• 01:27 If we go and roll this down, you'll notice that I've fed it 12 months.
• 01:31 So what it does is actually increments 12 months or one year away.
• 01:36 And returns the end of the month, so I have May 31, 1990 year later.
• 01:39 And I can prove this again with the date 1959, December 1.
• 01:43 We're gonna get December 31, 1960.
• 01:47 And if we wanna go three years in advance, 3 times 12 is 36.
• 01:51 So we can see from 1950 we're gonna come up to December 31st, 1953.
• 01:55 But we're not restricted with working with either 0s or whole years.
• 02:00 You can see here I can actually increment just four months.
• 02:03 Now because this is an August date, that's again gonna take me to December 31.
• 02:09 What if it was a November date, we went three months.
• 02:12 We're gonna get to February, 2006, which was also, obviously, not a leap year.
• 02:17 Now these are all cool, but I think where the really important function that
• 02:20 actually comes into this is that we can also use negative numbers.
• 02:24 Now check this out, when we roll this up with -1 for
• 02:28 January 6, 2014, it takes us back to December 31, 2013.
• 02:32 So we're actually backing it up one month.
• 02:35 And I can even back it up an entire year, so
• 02:38 July 7 becomes July 31 of the previous year by using -12.
• 02:42 EOMONTH is super useful, because I can enter a single date, and
• 02:47 then I can basically say, =EOMONTH of this particular date plus 1.
• 02:51 And I can keep rolling that across my table, and
• 02:53 I can build a full month end schedule out of it, very nice and easy.
• 02:57 So what's the next function, EDATE, what is this all about?
• 03:00 Well, EDATE works slightly differently.
• 03:03 If I go and say give me the start date and I say ,0 for
• 03:07 months, again, you'll notice that EDATE returns exactly the same thing.
• 03:12 Well, why would I wanna do that?
• 03:14 Well, you wouldn't.
• 03:15 You'd never really want work with 0 with an EDATE function because
• 03:18 where the real magic comes in is when we actually go and
• 03:21 provide it with a positive number of months.
• 03:24 Notice that now we've got May 10, 1990, off of a date of May 10, 1989.
• 03:30 So this is actually incrementing it forward a bunch but
• 03:33 it's keeping the day the same.
• 03:35 And as I scroll this down, you'll notice that all of these dates increment.
• 03:40 December 1, 1959 becomes December 1, 1960 when we feed it 12 months.
• 03:47 Our date 1950 just moves to 1953 with 36 months and when I play around
• 03:52 with the increment from August 14 and add four months, we get to December 14.
• 03:58 Likewise, you can see it also works backwards.
• 04:00 January 6 will roll back to December 6 of the previous year and
• 04:05 our July 7, 2007 will roll back to July 7, 2006.
• 04:09 Now the one thing you want to watch out for with EDATE,
• 04:12 let's just go and change this to a February 29, 2008.
• 04:15 Notice that the EDATE doesn't pull back February 29, 2009 because there isn't one.
• 04:22 And so it actually rounds it down to February 28.
• 04:25 But what if we actually go backwards?
• 04:27 Let's go and Ctrl+C this and we'll paste it right here where we're using -12.
• 04:32 And notice again, that if it back ups this particular date it's gonna go and
• 04:36 it's gonna pick up February 28 as well.
• 04:39 So in this particular case you're building a schedule that relies on these cells you
• 04:43 can actually end up shifting your values one way or another.
• 04:45 So that's something to be aware of when you're playing with these functions.

Lesson notes are only available for subscribers.