Excel reports and analyses typically include date calculations. But many Excel users have large gaps in their knowledge about this topic.
So in this article, I’m going to cover common date calculations, and offer a variety of ways that you can perform many of them.
Where I can think of several ways to perform the same calculation, I’ll mention the most commonly used version first.
But before I get into the calculations themselves, we need to cover some basic topics about Excel dates…
Date Serial Numbers
Excel uses a date serial number to specify a date. The serial number starts around 1900, and counts each day since the starting date. The starting date varies between the Windows or Mac operating systems, but I’ve never seen a report where the specific starting date mattered.
Excel typically doesn’t display date serial numbers in your formula bar. For example, if you enter 6/1/2015 in a cell, you’ll see that same information in your formula bar. Therefore, to see the actual date serial number, you’ll need to format your cell to display a non-date number format, like General.
Unfortunately, Excel doesn’t support negative date serial numbers. So you can’t use negative date serial numbers to represent dates prior to 1900.
Excel expresses the time of day as a decimal fraction of a 24-hour day. So, for example, if a date serial number ends with .25, the time of day is 6:00:00 AM…which is one-fourth of the way through a 24-hour day.
The First-of-the-Month Convention
When you want to display a date as a month and year, without displaying the day of the month, always use the first day of the month to define your date.
By always following this convention, you give yourself—and others who might use your worksheet—a widely used date convention to rely on.
This convention also can simplify your date formulas, as you’ll see shortly…
Common Date Functions
For years, Excel offered these four date functions for typical date calculations in reports:
- DATE(year, month, day) Returns the date serial number of a specified date.
- YEAR(serial_number) Returns the year for a date serial number.
- MONTH(serial_number) Returns the month number for a date serial number.
- DAY(serial_number) Returns the day number for a date serial number.
But in recent generations, Excel has added these two functions:
- EDATE(start_date, months) Returns the serial number of the date that is the indicated number of months before or after the start date.
- EOMONTH(start_date, months) Returns the serial number of the last day of the month before or after a specified number of months.
Let’s see how you can use these functions in common date calculations…
Simple Day and Week Sequences
Because the date serial number counts the number of days, you can add or subtract days by adding or subtracting some number to the beginning date.
This figure shows two ways to perform that kind of calculation.
Example 1 shows a simple calculation. It merely adds one day to the date serial number in cell B5 to get the serial number of the following day, shown in cell D5.
Example 2 performs the same calculation in a different way. You probably won’t use this version often. I included it because it does illustrate how the YEAR, MONTH, and DAY functions typically are used with the DATE function.
In both formulas, the number of days in column C can be a positive or negative number.
Adding or subtracting weeks is a similar issue. You just add weeks in groups of seven days, as shown for examples 3 and 4:
Of course, if you prefer that column C in this example show the number of weeks, rather than the number of days, you’d multiply the value in column C by 7 in your formulas.
Month Sequences for Dates that Use the First-of-the-Month Convention
This figure illustrates the most-common type of date sequence I’ve seen in business reporting. The report ends with data for a specified report date, and shows data for several months or quarters prior to that date.
That is, each prior date is a month or a quarter prior to the preceding date.
Monthly and quarterly reports like this always use the first-of-the-month convention for the date.
As an aside, notice that the report title includes a date that spans two cells. This is unusual because dates are formatted numbers, and Excel won’t display a number in more than one cell.
Therefore, to display that date I used this formula to convert the date serial number into formatted text that Excel will display correctly:
D1: =TEXT(D2,”mmmm, yyyy”)
The following examples illustrate calculations that you’ll typically use in reports like the one above:
Examples 5 and 6 return the same date serial number in this instance, using different worksheet functions. However, because the formula with the DATE function uses a day value of 1 for the function, it applies the first-of-the-month convention automatically. On the other hand, the EDATE function in example 6 returns the first of the month because the date in cell B16 uses the first-of-the-month convention.
Example 7 also returns the beginning date for the prior month. It offers the shortest formula, but it’s also the most-difficult to understand. So use it with care. Here’s how its formula works:
It starts with the date serial number for June 1 of some year. Then, because B17-1 returns the date serial number for the day before June 1, which is May 31, the section of the formula DAY(B17-1) returns the day value for that date, which is 31 in this instance. Finally, it subtracts that value of 31 from the date serial number in cell B17, returning the first day of May.
The formulas for examples 8 and 9 return the quarter-ending dates prior to June’s quarter-ending date. The formulas for these two examples work just like the formulas for examples 5 and 6.
Returning the Last Day of the Month
You also can start with a date serial number for any day of the month and return the date serial number for the last day of current, future, or prior months.
Example 10 shows the easiest way to perform the calculation these days. The formula in cell D23 returns the last day of the month that’s three months after the date in cell B23.
Example 11 shows how to perform the same calculation using the DATE function. Here, the formula adds one more month than the number specified in cell C24, and then it returns the date serial number using a value of zero for the day argument. Here’s the logic for that day value of zero:
The formula…
=DATE(2015,5,1)
…returns the date serial number for May 1, 2015. So the formula…
=DATE(2015,5,0)
…returns the date serial number for the preceding day, which is April 30, 2015.
Examples 12 and 13 show that when the same formulas use a month counter of zero, they return the last date of the current month.
Returning the First Day of the Month
In this case, the DATE function seems easier to use than the EOMONTH function; so I illustrate the DATE function first.
Example 14 shows that the DATE function can return the first day of any month before or after the starting date simply by adding the number of months to the month argument, and then using the value 1 as the day argument.
Example 15 shows that the EOMONTH function can perform a similar calculation. To do so, we find the last day of the month prior to the one we want, then add one additional day to the date serial number that EMONTH returns.
Examples 16 and 17 show that the same formulas can return the first day of the current month.
Year Sequences
Finally, we also can calculate year sequences easily in Excel.
Example 18 shows that the DATE function can return the same day and month of some other year by adding the year counter to the year argument of the DATE function.
Example 19 shows that the EDATE function can perform the same task. But because that function expects the counter to be in months, you must multiply the value in cell C38 by 12, as shown in the formula for cell D38.
