Example: Task Durations, Resource and Cost Estimation

The file Ch23.1.DatesAndResources.xlsx demonstrates that two dates can be subtracted to find the number of days between them. For a task that starts on a day (at the beginning of that day) and lasts until the end of another day, the total task duration is the difference between the dates plus 1 (for example, a task starting at the beginning of day 2 and lasting to the end of day 4 has a duration of 3 days). One may also use the DAYS function to calculate the task duration, if one remembers that the DAYS function captures a start-to-start (and not a start-to-end) duration, so that the result may need to be adjusted, depending on the context. The task duration can be multiplied by resource intensity requirements (such as full-time-equivalent people (FTEs) or costs per day, and so on) to calculate total resource requirements (see Figure 23.1).

Example: Keeping Track of Bookings, Reservations or Other Activities

The DATE function converts inputs corresponding to the year, month and day into a date.

The file Ch23.2.DATE.ToDate.xlsx shows an example, in which the year, month and day of each of a set of hotel reservations are entered, from which the DATE function creates the dates, and the DAYS function works out the length of stay (compared to the previous example, there is no need to add one to the result to obtain the correct figure in this context) (see Figure 23.2).

Example: Creating Precise Time Axes

The EOMONTH and EDATE functions can be used to create precise time axes. For example, EOMONTH works out the date of the month for a specified number of months after a starting date. If the starting date is set to be the beginning of a month, then the function can be used to work out full month periods to create a time axis automatically. EDATE works out the date that is a specified number of months after a starting date (e.g. 15 March is two months after 15 January). This is useful if the model periods are not aligned with the beginning of months or years. In addition, the knowledge of the exact number of days in a period may be required for detailed forecasting, such as the calculation of total production figures each quarter, when the production process runs every day or 24 hours per day.

The file Ch23.3a.DateAxis.EOMONTH.xlsx shows an example in which a quarterly axis is created using formulae based on a month-beginning start (1 January 2017) and an assumed time period of 3 months per column (the value in Cell C3 can be changed to create columns of any full-month time length: monthly, quarterly, annually or others) (see Figure 23.3). Note that the period length can also be calculated from the start and end dates (either using the difference between the dates or the DAYS function, as described earlier).

The file Ch23.3b.DateAxis.EDATE.xlsx shows a similar example using EDATE (see Figure 23.4).

Example: Calculating the Year and Month of a Date

The file Ch23.4.YearsAndMonths.xlsx shows the use of the YEAR and MONTH function to determine which year and (numerical) month correspond to a date (see Figure 23.5).

Example: Calculating the Quarter in Which a Date Occurs

Whilst Excel has YEAR and MONTH functions, there is (at the time of writing) no QUARTER function. If it were to exist, it would presumably have to have the flexibility to deal with the different definitions of quarters that are used around the world: the period January to March is widely considered as the first quarter, yet the British financial year ends at the beginning of April, for example.

The file Ch23.5.Quarters.xslx shows several ways to calculate the quarter of a date, after first working out its month (using MONTH), assuming that the period January to March represents quarter one. The first method uses the INT function, the second uses ROUNDUP and the third uses a lookup function based on a pre-defined table (these functions have been described earlier in the text, with the exception of the lookup functions, which are covered in Chapter 25) (see Figure 23.6).

Example: Creating Time-based Reports and Models from Data Sets

Given the year, month and quarter of any date, it is often possible to create reports from databases. Indeed, sometimes the most appropriate way to structure an entire model is so that it is driven from a data set, with the model essentially being a set of reports (and perhaps some further calculations based on these); this modelling approach was discussed in Chapter 5.

The file Ch23.6.DatesToModelBuild.xlsx shows an example in which a data set of items relating to the rental of a property (rental income, agent's costs, repair costs etc.) is used to create a quarterly report of revenues, total costs and profits. The date is used to calculate the year, month and quarter, from which a text key (such as “2014 Q1”) is created. The report (in the range K4:M12) is created using the SUMIFS function to sum items in the data set based on the key. Finally, the profit for each quarterly period is calculated from this report (Column N) (see Figure 23.7).

Example: Finding Out on What Day of the Week You Were Born

The WEEKDAY function can be used to find which day of the week a given date falls on. Note that the default convention is for the week to start on a Sunday; if one wishes the week to start on the Monday, then the optional argument (2) can be used.

The file Ch23.7.WEEKDAY.xlsx shows an example of each approach (see Figure 23.8).

Example: Calculating the Date of the Last Friday of Every Month

The Date functions can be used in combination. For example, for detailed cash flow planning calculations, one may wish to know the exact date of the last Friday of each month (for example, as the date on which a company's payroll will be paid).

The file Ch23.8.LastFridayofMonth.xlsx shows a series of calculations involving the EOMONTH and WEEKDAY functions, which calculate the date of the last Friday of the month given any input date within the month (see Figure 23.9).

Example: the DATEDIF Function and Completed Time Periods

In the previous example, we used the EOMONTH and WEEKDAY functions to work out the date of the last Friday in any month. Other similar examples involve calculating the number of months that have started since a given date (i.e. fully completed months plus the month under consideration; many contracts are such that, as soon as a month has started, the full month's payment is due). Several approaches are possible:

• Calculate the number of started months by using EDATE (rather than EOMONTH), appropriately combined with YEAR and MONTH. This exercise is left to the reader.
• Use the DATEDIF function. This computes the difference between two dates in a variety of different intervals, such as the number of years, months or days between the dates. For example, given two dates, it can provide the number of completed months (i.e. the number of started months). Although the function is potentially powerful, it is not clear (at the time of writing) to what extent it is a fully supported function (for example, although it is listed on the Microsoft Office Support website when searching for Excel Date functions, it does not appear on Excel's Date function menu).

The file Ch23.9.DATEDIF.xlsx shows examples of this function (see Figure 23.10).