Excel 2016: Date Functions

Updated 2017.10.15

· Learn how dates and times are represented internally in Excel.

· Learn how to format dates.

·
Learn how to use the **Today**()
function to get the current date.

·
Learn how to use the **Now**()
function to get the current date and time.

·
Learn how to use the **Date**()
function convert a month, day, and year into an Excel date (serial number).

·
Learn how to use the **DateDif**() function to determine how much time has elapsed between
two dates.

·
Learn how to use the **Month**(),
**Day**(), **Year**(), and **WeekDay**()
functions to extract individual fields from an Excel date.

To enter a date in a cell, simply type the date in the
format **mm/dd/yyyy** or
**mm-dd-yyyy**. Use
slashes or hyphens to separate the month from the day and the day from the
year. If you use any other separator character, it will look correct, but Excel
will __not__ represent it internally as a date and you will __not__ be
able to perform date arithmetic on it.

To enter a time in a cell, simply type the time in the
format **hh:mm** or **hh:mm:ss**. Append either **AM**
or **PM** (in either upper-case or lower-case) to the end of the time. The
seconds field (ss) is optional. You __must have a
blank__ between the time and the AM or PM. You cannot use periods (e.g. A.M.
or P.M.) Note that if you do not enter a date before the time, the number
0 will be to the left of the decimal point and the date 1/0/1900 will be
assumed (and while this is not a valid date, it does not cause a problem)!

You should __always__
enter years as 4-digit numbers (entering years as 2-digit numbers was the cause
of the "Year 2000 problem"). However, if you enter the year portion
of a date as a 2-digit number, you need to know how Excel will interpret it.
Excel interprets two–digit dates like this:

·
30-99 – are treated as part of the 20^{th}
century. That is, the digits "19" are put on the front of the number.

·
0-29 – are treated as part of the 21^{st}
century. The digits "20" are put on the front of the number.

Excel stores
dates using a **serial number.** Serial numbers are used to count the days.
The counting started on 1/1/1900.

The serial number 1 is January 1, 1900, serial number 2 is January 2, 1900, etc., like this:

1 |
1/1/1900 |

2 |
1/2/1900 |

3 |
1/3/1900 |

4 |
1/4/1900 |

5 |
1/5/1900 |

Etc. |

January 1,
2000, is 36,526 (note that this is 365 days * 100 years + 25 leap year days +
1).

Times are stored as a fraction of a day. For example, .5 is half a day, or 12:00 noon. .75 is three-fourths of a day, or 6:00 p.m., etc.

Dates and times are combined – the integer part of a serial number represents the date, and the fractional part of a serial number represents the time of day. So 12 noon on January 1, 2000 is represented as 36,526.50.

You can determine how much time has passed between two dates
simply by subtracting them. Since Excel stores dates as serial numbers,
subtracting two dates will give you the number of days from the first date to
the second date. Note that Excel may sometimes format these numbers
incorrectly; sometimes it will try to format the __difference__ between two
dates as a date, which will give a strange-looking result.

For some reason, the DATEDIF function is not documented in Excel. It is used to determine how many days, months, or years have passed between two dates. It will drop any fraction of a year. The format of the DATEDIF function is:

=DATEDIF(earlyDate, lateDate, type)

*earlyDate*
must be a date that comes before *lateDate*.

*Type* must be one
of the following:

· "d" or "D"—computes the number of days between the two dates

· "m" or "M"—computes the number of complete months between the two days (does not round up)

· "y" or "Y"—computes the number of complete years between the two days (does not round up)

Finding somebody's age in Excel can get a bit complicated,
but with the DATEDIF function, all you need to do is provide it with the two
dates, and "y" (for "year") as the third argument. For
example, if somebody was born on 9/1/1985 (see B1 below) and you want to find
out how old they are on 8/31/2010 (see B2), their age would be 24 because they
have not yet reached their 25^{th} birthday.

If we change the "Current Date" (B2) to any day
from 9/1/2010 (25^{th} birthday) to 8/31/2011, we will get 25.

To get the current date to always appear in a cell, enter
the **=TODAY()** function in the cell. Note that the
today() function has __no arguments__; however, the
parentheses are still required.

To get both the current date and the current time of day to
appear in a cell, enter the **=NOW()** function in
the cell. The now() function also has __no arguments__.

There may be times when you have a date (as a serial number) and you want to extract one of its fields: the month, the day, or the year. Excel has three functions for extracting these values.

The** MONTH()** function
requires a single argument – a serial number representing a date. It
returns a number in the range 1-12 representing the month part of that date.

The formula in B3 will return the number 12 (the month from B1).

After pressing the *Enter* key:

The **DAY(****)** function
requires a single argument – a serial number representing a date. It
returns a number in the range 1-31 representing the day of the month part of
that date.

The formula in B4 will return the number 25 (the day from B1).

After pressing the *Enter *key:

The **YEAR(****) **function
requires a single argument – a serial number representing a date. It
returns a number in the range 1900 and above (dates before 1/1/1900 are illegal
in Excel) representing the year part of that date.

The formula in B5 will return the number 2012 (the year from B1).

After pressing the *Enter *key:

The **WEEKDAY(****)**
function requires a single serial number representing a date. It will return a
number in the range 1-7 representing the day of the week, where Sunday is day
1, Monday is day 2, etc.

If you enter the =WEEKDAY(A1) in B1 below:

The number 7 will appear, telling us that the year 2000 began on a Saturday.

If you
already have a spreadsheet with the dates spread out over three columns (a
month column, a day column, and a year column), you may want to convert those
three values into a single date serial number (so you can use it in date
arithmetic calculations later). To do so, use the **Date(****)**
function. The Date() function requires three
arguments:

**Date(****year,
month, day)**

Note that they are __not__ in the usual m/d/y format that
we are used to, but are in decreasing order of magnitude (years are the biggest
units, then months, and days are the shortest)

Entering the formula, before pressing the Enter key:

After pressing the Enter key:

What was special about 7/20/1969?