Temporal Functions

Quick reference

• 00:04 This video actually demonstrates one of my personal favorite functions in Excel which
• 00:08 is EOMONTH.
• 00:09 Call these temporal functions, these are all about shifting time.
• 00:13 And certainly if you work in finance or if you're trying to build monthly schedules,
• 00:17 you'll find that these two particular functions here are fantastic.
• 00:21 The first one we're going to look at is EOMONTH.
• 00:24 This actually stands for end of month and it takes two parameters.
• 00:29 The first is a starting date.
• 00:30 We'll start here with February 5th, 1982, and
• 00:33 we're going to go with the number of months that we would like to advance it.
• 00:38 We're gonna go and click on the value in column B here, which is 0.
• 00:42 And watch what ends up happening with this function, is it returns February 28, 1982.
• 00:48 So it's actually picking up the last day of February because we used 0.
• 00:53 It's picking up the last day for that current month.
• 00:55 But this is where things get interesting with EOMONTH,
• 00:58 is we can actually drag this down and take a look here, where we've got May 10th,
• 01:03 1989, and we say give me the end of the month, 12 months from now, and
• 01:07 it'll come back with May 31st, 1990 December 1st, 1959,
• 01:12 12 months later is December 1st or 31st, 1960, and this will work.
• 01:16 We can go three years in advance by using 36 months,
• 01:20 we can go four months in advance to get us to December 31st, 1987 and
• 01:25 it even works with negative numbers.
• 01:29 And this is really cool because from January 26th, 2014,
• 01:32 if we back it up and say go minus one month, one month negative,
• 01:36 that's gonna give us December 31st, 2013.
• 01:39 So the end of the prior month.
• 01:41 These are really, really useful and I'll show you the way that I use these all
• 01:45 the time is I'll start off with a schedule of say January 31st 2015, and
• 01:49 I can run a nice EOMONTH function comma one.
• 01:55 Okay, so we're gonna advance this one month.
• 01:58 And remember the F3 here is relative.
• 02:00 I've not locked this in as absolute.
• 02:03 We'll just format this as a date, get that a little bit wider and
• 02:08 you can see now that as we drag this across, and we widen up our columns.
• 02:14 We can actually get a nice schedule of month end dates that will change
• 02:19 whenever I go and add a new year or change the year.
• 02:22 So that's really kinda cool.
• 02:25 I'll just get rid of those and we'll go back to the next function, which is EDATE.
• 02:31 EDATE works similar to EOMONTH.
• 02:34 It takes a start date and it takes a number of months.
• 02:39 But what EDATE does is it advances to the same day in the next month.
• 02:44 So EDATE comma 0 is kinda nonsensical, it gives you the same date.
• 02:47 But when you go EDATE comma 12, we get May 10th, but
• 02:51 instead of 1989 we're 12 months out.
• 02:54 We're now in 1990, which is kind of cool as well.
• 02:57 So these are good little functions for actually returning the same day, years,
• 03:02 or months from now and like EOMONTH, they
• 03:07 actually work with negative parameters as well to say January 26th, 2014.
• 03:11 One month before that was December 6, 2013 and
• 03:15 12 months before July 7, 2007 was July 7, 2006.
• 03:21 These functions are super, super useful for shifting your dates backwards and
• 03:25 forwards, they are February 28th, 29th aware, which is really cool,
• 03:30 one thing you want to be a little careful though is if you look at your EOMONTH for
• 03:34 February 28th and say EOMONTH 12 months from now,
• 03:38 it should come up potentially with February 29th.
• 03:42 If you're using EDATE,
• 03:43 you won't necessarily have that happen because your EDATE for
• 03:47 February 29th, the next year will be February 28th would be the equivalent day.
• 03:52 And thereafter it will actually continue to look at February 28th.
• 03:55 So you've gotta watch that little trick in there.
• 03:58 One thing I just want to talk about with compatibility, it's becoming less and
• 04:01 less of an issue every day, but
• 04:03 these functions actually have existed in Excel for all time.
• 04:07 They were in something called the Analysis Tool Pack which was even installed
• 04:11 with Excel 97.
• 04:11 The challenge was it wasn't activated by default and
• 04:15 these didn't become standard in the product until 2007.
• 04:18 So if you are sending a file to somebody who's using Excel 2003,
• 04:21 you need to get them to go into the add ins menu and
• 04:27 actually activate the Analysis Toolpak, and then it will work just fine.
• 04:33 So you go to Tools, AddIns and check the box next to Analysis Toolpak and say OK.
• 04:38 But hopefully that won't happen to any of you because most people are on
• 04:42 at least Excel 2007 if not higher, and
• 04:44 these things are all built in now which is great because they're just so darn useful.

Lesson notes are only available for subscribers.