Temporal Functions

Quick reference

• 00:04 Now in this video we're going to look at two of the coolest functions in Excel these are
• 00:08 temporal functions which are basically used for shifting dates one way or another.
• 00:13 The two functions we're going to examine are EOMONTH and EDATE which
• 00:17 have actually existed in Excel for a long longtime but they only really
• 00:21 became integrated into the product in Excel 2007, 2010, and 2013.
• 00:27 The way they work is this: we've got a list of dates down here and we've also got a
• 00:33 number of months and these are going to be used to offset from these particular dates which will return a date.
• 00:39 So the formula we're going to use is =EOMONTH
• 00:43 and it asks for a start date. No problem we'll give it February 15th and
• 00:47 we'll give it a number of months and we're going to offset this zero months and we can see
• 00:51 that when we do that it comes back with February 28. So
• 00:55 what you can see is that EO stands for "end of" and it will actually
• 01:00 generate the correct month and date for
• 01:02 whatever month we have if we feed it the month
• 01:05 number of zero. We can do that either by feeding it the range or of course by just feeding a zero in here as well.
• 01:10 Now as I drag this down
• 01:14 you'll see that it works all the way down the list so February 29, 12 months later
• 01:20 is February 28 2013. So it even deals correctly with the shifting of February 28 and 29th.
• 01:27 On February 28 here it'll kick out to February 28, 2014 because
• 01:34 12 months later it is. On the 29th though there is no 29th in 2013 which is why it returned the 28th.
• 01:40 From February 28, 2013 jumping it out 36 months or three years we'll actually get to February 29, 2016 so this is correct as well.
• 01:49 Four months from June 21 is October 31 for the month end. You
• 01:52 can see that it keeps on going down and it works all the way through which is awesome.
• 01:56 Now you can also feed this a zero which will get to the end of the current month.
• 02:00 You can also back it up by going with negative numbers so from
• 02:04 September 12 we back up and we get August 31 the previous month end. Which is great! And from September 12,
• 02:10 2013 backing off 12 months we get back to September 30, 2012 which is great.
• 02:15 If you wanted to change that a little bit more you could go -13, -28 you could do however many number of months you want.
• 02:22 The same thing happens with the EDATE function. So if we say =EDATE and we
• 02:28 open our brackets it asks for a start date and the number of months to offset.
• 02:33 Go and feed these same values in
• 02:35 and what this will do, well it doesn't do anything with the current date. But if we run this down now
• 02:43 you can see that this one advances two from February 29th to the EDATE which is February 28th because there is no 29th.
• 02:53 The 28th advanced to the 28th so far no real differences but look at this one here:
• 02:58 36 months out from the 28th is still going to be the 28th.
• 03:02 Well, that's interesting this one was the 21st of October so what EDATE
• 03:06 is doing is actually moving forward a certain number of
• 03:11 months to pick the same day. Which is really neat.
• 03:15 That's why these guys here up with February actually are working this way is that there is no
• 03:19 February 29th which is why it picks up the last day from February 28th so that's what's happening in that particular area
• 03:24 Again you can see it also backs up, so from September 12 we move back to August 12
• 03:29 and from September 12, 2013 we can move back an entire year to September 12, 2012.
• 03:37 Now I mentioned that these functions weren't around sort of with Excel 2003 and earlier. They always actually were it was just that you had to
• 03:45 install the Analysis Tool Pack and the funny thing is that the Analysis Tool Pack
• 03:49 shipped with Excel for years and was just never activated by default.
• 03:53 So if you ever send a formula using EOMONTH or EDATE to a user
• 03:57 that's on Excel 2003 or earlier, they might phone you up and say there's a whole bunch of
• 04:00 #NAME? all through my workbook. It's real easy to fix, all you do is you say ok no problem go to tools->
• 04:08 add ins and check the box next to Analysis Toolpack and say ok and that's it. And at that point it activates the
• 04:15 EOMONTH and EDATE and a whole bunch of other really cool formulas as well
• 04:19 for that particular user. And they will only ever have to ask you once and once their worksheets
• 04:22 or their Excel instance is setup it'll work like that forever. So
• 04:27 just because these formulas weren't always quite available in 2003 they've always been
• 04:32 there so there's no reason really not to use them because they are absolutely
• 04:36 perfect for a lot of different scenarios working with financial numbers.

Lesson notes are only available for subscribers.