Asking for a Date

You can’t build a forecast model without using time series. In this article, we look at the trials and tribulations of including dates in Excel 2003 (and earlier) versus Excel 2007 in particular. By Liam Bastick, Director with SumProduct Pty Ltd.

Query

I regularly have to build forecast models with dates going across the top of my spreadsheet. Do you have any tips about the best way(s) to do this?

Advice

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.

In this example, a monthly model has been constructed starting in July 2010. The dates in cells J6 onwards are formatted to show only the month and year. This can be performed easily by selecting the date (here, 31 July 2010) and then formatting the cell (CTRL + 1 in all versions of Excel).

A comprehensive explanation of how to format dates, numbers and other cell contents can be found in the SumProduct Number Formatting Excel article.

More interestingly, if the General category were to be selected instead, we note that the Sample (circled in red) would be displayed as follows.

In other words, 31 July 2010 is no more than a number: 40,390. Microsoft Excel for Windows and for Windows NT support what’s called the 1900 date system. This means that 1 January 1900 is considered to be day 1 by Excel, 2 January 1900 is day 2 and so on.

Extrapolating, 31 July 2010 would be day 40,389 (not 40,390). The reason that the 1900 date system views it as one day later is because this system considers 1900 to be a leap year – which it wasn’t! Years ending in ‘00’ have to be divisible by 400 to be a leap year, so 1 March 1900 in this system was day 61 rather than day 60.

Most modellers are not concerned about how the days are numbered as long as Excel calculates correctly. However, early Macintosh computers used a different start date to prevent issues with the 1900 leap year and used 1 January 1904 as the start date. This is known as the 1904 date system and the date values can be calculated as:

Care has to be taken with Excel files opened on both PCs and Macs, as the date systems may vary. You can force Excel to use a particular date system as follows:

Clearly, dates are not as easy to manipulate as you might think. Extracting the day, month or even the year from any given date is not straightforward because the date is really a number known as a serial number.

To extract the day, month or a year has to be undertaken using the following three functions:

• DAY(serial_number) gives the day in the date (for example, DAY(31/7/10) = 31);
• MONTH(serial_number) gives the month in the date (for example, MONTH(31/7/10) = 7); and
• YEAR(serial_number) gives the year in the date (for example, YEAR(31/7/10) = 2010).

It is just as awkward the other way round. If the day, month and year are already known, the date can be calculated using the following function:

(for example, DATE(2010,7,32) = 1 August 2010, etc.).

Since dates are nothing more than serial numbers, they behave just like formatted numbers in Excel, for example, 31-Jul-10 + 128 = 6-Dec-2010.

Time series analysis

Time series still cause us problems. If we want to have the month end date in each column, we 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:

The ‘End of month’ function therefore calculates the end of the month as the number_of_months after the specified_date. For example:

• EOMONTH(31-Jul-10,0) = 31-Jul-10;
• EOMONTH(3-Apr-05,2) = 30-Jun-05; and
• EOMONTH(29-Feb-08,-12) = 28-Feb-07.

Although the examples use typed in dates, for it to work in Excel, it is best to have the specified_date either as a cell reference to a date or else use the DATE function to ensure that Excel understands it is a date (otherwise the formula may calculate as #VALUE!).

In some instances (for example, appraisal of large scale capital infrastructure projects), the dates may need to be for the same day of the month (for example, the 15th) rather than for the month end. The DATE function can often be used to calculate these dates – unless it is near the end of the month as problems may arise with February, April, June, September and November.

A function similar to EOMONTH, EDATE, can be used instead:

The ‘Equivalent day’ function therefore calculates the date that is the indicated number_of_months before or after the specified_date. For example:

• EDATE(15-Jul-10,2) = 15-Sep-10;
• EDATE(3-Apr-05,-2) = 3-Feb-05; and
• EDATE(29-Feb-08,-12) = 28-Feb-07.

If an equivalent date cannot be found (as in the last example), month end is used instead.

Similar to EOMONTH, it should be noted that although these examples also use typed in dates, for it to work in Excel, it is best to have the specified_date either as a cell reference to a date or else use the DATE function to ensure that Excel understands it is a date (otherwise the formula may calculate as #VALUE!).

Common problems I: Analysis ToolPak

Regular readers will note that I have started putting warnings on the front page of my example Excel workbooks, viz.

Certain functions are not in the ‘main library’ of Excel functions and will give rise to #NAME? errors in Excel if not recognised. Unfortunately, EOMONTH and EDATE are two such functions.

To ensure they work / are recognised correctly, the Analysis ToolPak needs to be added in. To ensure it is, in all versions, use the keyboard shortcut ALT + T + I to load up the Add-Ins dialog box:

It is important that model developers and end users alike have this ToolPak added in for all versions of Excel up to and including Excel 2003, otherwise these functions will not work.

Common problems II: Excel 2007

In Excel 2007, EOMONTH and EDATE have been added to the ‘standard’ functions database in Excel so it is not necessary to ensure that the Analysis ToolPak has been added in. This avoids our first common problem, but unfortunately, it sometimes causes a bigger one due to an apparent compatibility issue between Excel 2007 and earlier versions of Excel.

If Excel 2003 Analysis ToolPak functions such as EOMONTH or EDATE are used in an Excel 2003 (or earlier version) file that is then opened in Excel 2007, the formulae that incorporate these functions may be replaced by =#/N/A.

Some experienced users believe that if you ensure the Analysis ToolPak is switched on in Excel 2007 before you open the file (albeit in compatibility mode), the problem will be avoided, but the modelling community as a whole is not sure this is the case necessarily.

The only sure fire way I have found to avoid this problem is to not use these functions if the file will be opened in Excel 2007 and earlier versions of Excel. This means that you need to find equivalent formulae that do not use Analysis ToolPak formulae.

For developers and users in this predicament you might wish to consider the following alternative formulae. We can’t guarantee that these formulae will replicate EOMONTH and EDATE – if you need to rely on these alternatives, please convince yourself of their veracity independently.

For EOMONTH(specified_date,number_of_months) use:

DATE(YEAR(specified_date),MONTH(specified_date)+
IF(number_of_months,ROUNDDOWN(number_of_months,0),)+1,)

For EDATE(specified_date,number_of_months) use:

MIN(DATE(YEAR(specified_date),MONTH(specified_date)+
ROUNDDOWN(number_of_months,0),DAY(specified_date)),DATE(YEAR(specified_date),
MONTH(specified_date)+ROUNDDOWN(number_of_months,0)+1,0))