Locked lesson.

Upgrade

About this lesson

Illustrations and issues with the EOMONTH function.

Exercise files

Download this lesson’s related exercise files.

EOMONTH.xlsx
12.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))
Login to download
  • 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.

Gift this course