Locked lesson.
About this lesson
Illustrations and issues with the EOMONTH function.
Exercise files
Download this lesson’s related exercise files.
EOMONTH.xlsx12.3 KB EOMONTH - Solution.xlsx
12.5 KB
Quick reference
EOMONTH
Discover how to use EOMONTH function in a formula.
When to use
Dates are commonplace in models and usually run across one of the top rows in an Excel worksheet as part of a time series analysis: If it is necessary to have the month end date in each column, you cannot simply take the previous month’s date and add a constant to it, since the numbers of days in months vary. Fortunately, there is a function in Excel that will perform this calculation for us: EOMONTH(Specified_date,Number_of_months)
Instructions
Overview
Syntax: EOMONTH(Specified_date,Number_of_months)
The “End of month” function calculates the end of the month as the Number_of_months after the Specified_date. For example:
- EOMONTH(31-Jul-20,0) = 31-Jul-20
- EOMONTH(3-Apr-05,2) = 30-Jun-05
- EOMONTH(29-Feb-08,-12) = 28-Feb-07
Example
- The formula =IF(E$17=1,EOMONTH($D$14,0),EOMONTH(D16,1)) is explained below:
- An IF function is used which says: If the ‘Counter’ = 1 then return the last day of the month from the start date (cell D14), with 0 months added on =IF(E$17=1,EOMONTH($D$14,0),
- The second part of the formula says: If the ‘Counter’ is any number other than 1, then return the last day of the month from the previous period end (cell D16), plus 1 EOMONTH(D16,1))
- 00:04 We're almost there with the functions now.
- 00:07 Next one coming up is the one I refer to, no one else does,
- 00:11 just me, as the Old McDonald function.
- 00:14 What is he on about this time?
- 00:15 Old McDonald had a function, EO-EOMONTH.
- 00:22 EO actually stands for, end of, end of month.
- 00:25 So what you do, is you actually define a start date and then you say how many
- 00:28 months from that start date do you want to determine the end of the month?
- 00:32 This might sound quite simple, but it's not because dates are a bit of a problem.
- 00:38 Not a huge problem, cuz that's what the function's there for, but let me explain.
- 00:44 Let me show you why working with dates can be fun and games.
- 00:48 Let's take the 17th of August, 2018 here, in cell D4.
- 00:54 All I have to do is go equals that cell, 43,329.
- 01:01 What?
- 01:02 Ctrl+1 to format cells.
- 01:04 I formatted it as a number.
- 01:07 Got you.
- 01:08 This is the serial number in Excel.
- 01:11 All dates are serial numbers.
- 01:13 The first of January, 1900 is serial number one, the second of January, 1900,
- 01:18 is serial number two and so on.
- 01:21 The reason they did this, is that when you got to the year 2000,
- 01:24 you won't have the millennium bug where you would have division by zero errors,
- 01:27 because everything's just a serial number.
- 01:29 And that's fine.
- 01:31 But if I just gave you a number at random,
- 01:33 would you actually know what month 38,412 was?
- 01:37 I doubt it, which is why we need functions that will actually calculate the day.
- 01:46 The month.
- 01:53 And the year.
- 01:58 Pretty simple, and
- 02:00 we can then reconstruct the data if we want to by using the date function.
- 02:05 Start with the year first, then the month, then the day.
- 02:10 Simple.
- 02:12 Back to that again.
- 02:13 You can do it the other way round.
- 02:15 This here is just equal to that cell, and
- 02:18 all that's happened if we go to format cells, is it's been formatted as a date.
- 02:23 Now this cell and this cell have been formatted the same.
- 02:26 So why does this one give a serial number, whereas this one still retains the date?
- 02:31 It's because it's not a date.
- 02:34 This is actually text.
- 02:36 If I actually show you this, you see it is ready to be a number.
- 02:41 Showing as text, showing this is text.
- 02:43 This can cause a problem sometimes especially when you're downloading data
- 02:47 from third party management information systems which is Bloomberg, Reuters,
- 02:52 Capital IQ and many other pieces of software that are out there.
- 02:55 Because sometimes they will actually download data as text.
- 03:00 We need to convert it to a number.
- 03:03 Now, multiplying by one wont necessarily work here.
- 03:07 What you've actually got to do is use an obscure function that a lot of
- 03:10 people don't know, called DATEVALUE.
- 03:14 If you DATEVALUE of this text, then it works, 8th of April 20.
- 03:20 If I actually change that to a number, it will actually change to a number, lovely.
- 03:29 That's why we need EOMONTH.
- 03:31 You see with EOMONTH,
- 03:32 everything's really a serial number even if it looks like a date.
- 03:35 So whilst I might know this looks like the end of that month's gonna be the 31st of
- 03:40 August 2018.
- 03:41 To actually do this, if I didn't know either month I'd
- 03:45 have to do something like, =date( the year of this,
- 03:50 the month I'd add 1 to, and then I make it it the naughtth,
- 03:55 because that would make it the last day of the previous month.
- 04:00 There you go the 31st of August 18, but
- 04:03 I can just use instead of that horrible thing, =EOMONTH, end of month.
- 04:10 And that at how many months?
- 04:12 Well, we'll make it that cell which isn't defined at the moment so
- 04:15 it'll be 31st of August 18.
- 04:16 Cuz it's treating this cell as 0.
- 04:19 If I make it 1 though, it'll be the 30th of September 18.
- 04:23 2, the 31st of October.
- 04:26 -1, the 31st of July.
- 04:29 -2, 30th of June.
- 04:31 Simple, yes?
- 04:36 What's this relevant for?
- 04:37 Well, in financial modelling, we need a start date in a model.
- 04:40 And then we need to put period ends in here, so
- 04:42 we'll need to write a consistent formula in here.
- 04:45 So bearing in mind we've already got a counter,
- 04:49 I'll go equals, if it's the first period.
- 04:53 So if this equals one, then we'll take the end of the start date month, here.
- 05:00 Otherwise, we'll take the end of the month and
- 05:04 the previous period and add one month to it.
- 05:07 So I'll take this cell and we'll go comma one.
- 05:12 Now that's acceptable as hard code in a form.
- 05:15 Remember we talked about consistency, transparency, flexibility and robustness.
- 05:19 The one that is something which is often done in modeling to put the date in.
- 05:23 So I copy that across,
- 05:24 you'll get the dates for the model are completely flexible.
- 05:28 So if if I change this to the ninth of September, 2009 instead, it still works.
- 05:36 Nice, simple way to get dates, EOMONTH is your friend.
Lesson notes are only available for subscribers.