Date Formulas
Nineteen Date Calculations in Excel
Here are the mostcommon types of date calculations used in
Excel reports and analyses.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(Download the workbook with the following examples.)
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 nondate 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
24hour day. So, for example, if a date serial number ends
with .25, the time of day is 6:00:00 AM...which is onefourth of
the way through a 24hour day.
The FirstoftheMonth 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 FirstoftheMonth
Convention
This
figure illustrates the mostcommon 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 firstofthemonth
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 firstofthemonth 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 firstofthemonth convention.
Example 7 also returns the beginning date for the prior
month. It offers the shortest formula, but it's also the
mostdifficult 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 B171 returns the date
serial number for the day before June 1, which is May 31, the
section of the formula DAY(B171) 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 quarterending
dates prior to June's quarterending 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.
Take Your Next Steps
You can take the next steps with this in two ways. First, you can
download
the completed workbook here. By doing so, can start
to experiment with my examples immediately.
Second, if you want to improve how your reports and analyses
work with dates, I can help you in
two ways. To learn more, see
Excel Coaching and Consulting.
