Temporal Functions

Quick reference

• 00:03 >> In this video, we're going to look at temporal functions in Excel.
• 00:08 And basically what a temporal function is,
• 00:10 is it's something allows you to shift dates either backwards or forwards.
• 00:15 We've got two really cool functions that we can use to do this very easily.
• 00:19 We've got EOMONTH, which stands for end of month, and we've got edate,
• 00:22 which allows us to push, it replicates the exact same feature as the EOMONTH
• 00:27 function, but it actually advances us forward to the same day and
• 00:30 the next month, or the same day and the previous month.
• 00:34 So the way this one works is we can start of in a cell, we will say equals EOMONTH,
• 00:39 and when we open our brackets, it asks us for our start date.
• 00:42 So I am just gonna go and pick off this one over here for
• 00:45 1982-02-05, and I'm gonna say, you know what?
• 00:49 This is February 1982, what I'd like to do is, I'd like to say,
• 00:53 give me the number of months to offset it, and we're gonna pick zero.
• 00:57 And we'll close the bracket and we'll enter, and
• 01:00 what it's gonna do is it's gonna return the last day of February in 1982.
• 01:04 So it automatically calculates and picks out February 28th.
• 01:07 Now this is great because the EOMONTH function is February 28th, 29th aware.
• 01:12 So it will actually pick up February 29th if it's a leap year, which is great.
• 01:18 You'll notice that, as we run this function down here, we use 12
• 01:23 months from 1989, May 10th, and it's gonna pick up the end of the month for May.
• 01:30 But of 1990 the next year, 12 months ahead.
• 01:33 In this case here we're gonna go with December, it'll return December 31st for
• 01:37 1959 and then advances it, cuz it's doing a 12 months, which gets us to 1960.
• 01:42 So this is kind of a neat thing.
• 01:44 Now what you'll notice as well though is we can go forward for four months or
• 01:47 three months.
• 01:48 In the case of November 13th, we get to February 28th, so that's good.
• 01:51 And then we see some negative numbers.
• 01:54 Well look what happens when we actually use negative numbers.
• 01:57 If we go to -1, you would expect that 0 would give us January 31st,
• 02:02 but in fact this actually takes us minus one month so
• 02:06 it backs us up to December 31, 2013.
• 02:09 So this great because we can move backwards and forwards.
• 02:12 The only thing that we need to remember is zero gives us the current month,
• 02:17 a positive number gives us that many months from now, or from the date, and
• 02:22 a negative number actually backs up.
• 02:23 If I wanted to go back an entire year, the month end for the year previous, we can
• 02:28 actually go back, and we've got July 31st from the previous year, so that's great.
• 02:34 EDATE is a very similar function to EOMONTH.
• 02:37 When we say EDATE, we again provide the start date, and if we were to give it,
• 02:42 say zero months, it's not really gonna do anything different for us here.
• 02:46 It should return the same date.
• 02:47 But what happens if we go 12 months?
• 02:50 Let's go and just drag that one down.
• 02:52 Notice that it's still May 10th, 05-10, but
• 02:57 instead of being 1989 it's now forward that 12 months into 1990, which is great.
• 03:03 So again, we can run these forward and we can actually look at
• 03:07 all these dates as they move both backwards and forwards in time.
• 03:12 Notice here this guy has actually backed up one
• 03:17 month well to December from January, but it's got the same day of the sixth.
• 03:23 Again this is a February 28th, 29th aware function, so if you try and take February
• 03:28 29th and advance it by one month, it's gonna pick up the 29th of the next month.
• 03:33 If you try and take the 31st of January with one month with EDATE or
• 03:39 with EOMONTH, it's gonna pick up February 28th or 29th, okay?
• 03:42 So that's one thing to be aware of there, is that that can have a little
• 03:45 bit of weird interplay if you extend a schedule a long way.
• 03:49 But the nice piece is it won't return an error, and that's really, really useful.
• 03:53 So these are great functions.
• 03:55 I say if you can master these, it'll be very,
• 03:57 very useful if you're if you're working with dates and
• 03:58 it's a function that a lot of people actually don't have on their tool box.
• 04:02 This will make you look like a master right of the bat if you can start busting
• 04:05 out one of these out on your daily work.

Lesson notes are only available for subscribers.