 Excel 2019/365: Date Functions

Updated 22019-08-08

Objectives

·        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.

Entering a date in a cell

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.

Entering a time in a cell

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)!

Entering two-digit years in Excel

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 20th century. That is, the digits "19" are put on the front of the number.

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

How Excel Stores Dates And Times

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). This is actually incorrect, because there were only 24 leap year days (1900 was not a leap year), but Microsoft deliberately put this bug into Excel to make it compatible with the Lotus 1-2-3 spreadsheet.

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.

Date arithmetic

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.

DATEDIF(earlyDate, lateDate, type)

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)

Example

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 25th birthday. If we change the "Current Date" (B2) to any day from 9/1/2010 (25th birthday) to 8/31/2011, we will get 25. TODAY(). The current date function

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.

NOW(). The current date and time function

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.

MONTH(serialNumber)

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.

Example

The formula in B3 will return the number 12 (the month from B1). After pressing the Enter key: DAY(serialNumber)

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.

Example

The formula in B4 will return the number 25 (the day from B1). After pressing the Enter key: YEAR(serialNumber)

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.

Example

The formula in B5 will return the number 2012 (the year from B1). After pressing the Enter key: WEEKDAY(serialNumber)

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.

Examples:

If you enter the =WEEKDAY(A1) in B1 below:  The number 7 will appear, telling us that the year 2000 began on a Saturday.

DATE(year, month, day). Converting a month, day, and year into an Excel date

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)

Example:

Entering the formula, before pressing the Enter key: After pressing the Enter key: What was special about 7/20/1969?