Locked lesson.
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 - Begin.xlsx25.4 KB Temporal Functions - Complete.xlsx
25.6 KB
Quick reference
Temporal Functions
Working with the EOMONTH and EDATE functions that shift dates easily from one month or year to another.
When to use
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.)
Compatibility
- The EOMONTH and EDATE functions can be used immediately in Excel 2007 or higher
- Even though the Analysis Toolpack (which holds these functions) existed and was installed with Excel since at least Excel 97, it was not activated by default
Activating the Functions in Excel 2003 and earlier
- The user must go to Tools, Addins, check the box next to Analysis Toolpack, and click OK
- Once done, that user will not have to make this change again, as it persists on the user’s profile
- 00:04 I'm super excited to be able to show you this function because honestly EOMONTH is
- 00:08 actually one of my all-time favorite functions in Excel.
- 00:11 And I know that sounds weird, but I'm an accountant, and
- 00:14 everything about my life always revolves around month-end.
- 00:17 So what EOMONTH allows me to do is it allows me to take a date and
- 00:21 convert it into the month and day and that is so important to my work.
- 00:25 So here's how this works.
- 00:27 I'm going to to say =, going to type in EO that's enough to get to EOMONTH.
- 00:31 I'm just going to press tab to commit it.
- 00:33 We're going to grab a start date, and
- 00:35 then I'm going to ask it to give me a month-end zero months from now.
- 00:39 Why would I do that?
- 00:41 Well, because February 5th, I want the month end, which is February 28, 1982.
- 00:47 And the important thing that you'll realize here is if you go back and
- 00:50 check your calendar for 1982, there were indeed only 28 days in February.
- 00:55 All right, so that's cool.
- 00:56 Now, let's take a look at this next one, though, we got February 29, 2008.
- 01:01 Let's go and flex that date by 12 months to get the month-end.
- 01:04 And what we get is we get February 28, 2009.
- 01:09 And this is important to realize because that is the right date.
- 01:12 There are 29 days in February 2008.
- 01:14 It's impossible for there to be 29 days in
- 01:17 February 2009 because there's a leap year component that goes on here.
- 01:22 This is not giving us March 1st, because it's asking for the EOMONTH.
- 01:25 It is giving us the February 28, which is perfect.
- 01:30 Likewise, if I go and roll all these things down here you can see that if we
- 01:34 go for the month-end 12 months after December 1st, 1959, of course,
- 01:38 we'll get to December 31st, but 12 months out should be 1960.
- 01:42 And that's what we get.
- 01:44 December 20th, 36 months out, that's three years.
- 01:48 Should give us December 31st, but instead of 1950, we'll have 1953.
- 01:53 And you can see this is working nicely.
- 01:55 August, four months, well that's September, October, November.
- 01:58 That is December, that is the fourth month.
- 02:00 And that works out quite nicely there.
- 02:02 And if we look at November here we're going three months, well,
- 02:05 that's going to be December, January, and February.
- 02:07 And the nice thing about this one, again, it is February 28th,
- 02:11 29th aware, and returns is the correct date here.
- 02:14 But the cool thing about this function is that it doesn't just accept zero or
- 02:18 positive numbers.
- 02:19 It also accepts negative values.
- 02:21 So when we take a look at January 3rd, 2014, and we say, give me minus 1 month,
- 02:27 and give me the month end, it's going to take that January 3rd date,
- 02:31 back it up by a month, which is December 3rd, and give us the month-end,
- 02:35 which is naturally December 31st of the previous year.
- 02:39 That is great.
- 02:40 want to go 12 months back from February 28, sorry, February 29th, 2008.
- 02:45 What do we get?
- 02:45 February 28th, 2007.
- 02:48 Awesome, awesome feature.
- 02:50 Now, what is EDATE?
- 02:52 Well, EDATE is actually very similar to EOMONTH.
- 02:55 We provide it with a date, we say how many months different would you like me
- 03:00 to shift that date and return the same day, x months later?
- 03:04 Now, in this case, I'm returning the exact same day because I've shifted zero months.
- 03:09 Why would I ever do that?
- 03:09 I probably wouldn't.
- 03:11 But you can see how it compares when we actually roll
- 03:15 it down into the other values.
- 03:17 In this case here, February 29th, we're shifting it by an entire month,
- 03:20 we get February 28th, the same as we would get for an EOMONTH function.
- 03:24 But when you look at December 1st, 1959, and we shift it 12 months, unlike
- 03:28 EOMONTH because it's the end of the month this one now gives us December 1st, 1960.
- 03:34 Notice, August 14th gives us December 14th, that's four months later.
- 03:38 This is not 365 days.
- 03:41 It is leap year aware, which is great because you can actually
- 03:46 get the same day next year or the same day next month.
- 03:50 In addition, just like EOMONTH, you can also back things up.
- 03:54 So you'll notice here that we've actually shifted this back one month from January
- 03:58 3rd, we get to December 3rd, and from February 29th, we get to February 28th.
Lesson notes are only available for subscribers.