🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Temporal Functions

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Learn to work with the EOMONTH and EDATE functions that easily shift dates from one month or year to another.

Exercise files

Download this lesson’s related exercise files.

Temporal Functions.xlsx
14.3 KB
Temporal Functions - Solution.xlsx
14.5 KB

Quick reference

Topic

Temporal functions.

Description

Working with the EOMONTH and EDATE functions that shift dates easily from one month or year to another.

Where/when to use the technique

The functions are extremely useful and easy to use when you need to return either a date from a different month or year, or when you need to determine the end of a given month.

Instructions

EOMONTH

  • Returns the end of the specified month

=EOMONTH(A1,-1)

Returns the end of the month previous to the date in A1

=EOMONTH(A1,0)

Returns the end of the month for the date in A1

=EOMONTH(A1,1)

Returns the end of the month after the date in A1

  • The negative and positive month offsets can be as high as you wish (12 is a full year, 24 is 2 years, etc.)

EDATE

  • Returns the same day in the specified month
  • Is Feb 28/Feb 29 aware

=EDATE(A1,-1)

Returns the same day for the month previous to the date in A1

=EDATE(A1,0)

Returns the date in A1 (the day offset by 0 months)

=EDATE(A1,1)

Returns the same day for the month after the date in A1

  • The negative and positive month offsets can be as high as you wish (12 is a full year, 24 is 2 years, etc.)
Login to download
  • 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.

Formula Auditing
04m:59s
VLOOKUP with Approximate Match
05m:13s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement