Fabulous Examples of Excel Functions: Date and Time

This tutorial provides important information about working with dates and time in Microsoft Excel. Then it proceeds to discuss the three popular date and time functions: TODAY, NOW, and DATEVALUE.

Author: Patricia Lynn

Don't forget that every Excel function begins with an equal = sign unless the function is imbedded inside of another function. Click on a link to go directly to that topic or function.

Overview of Dates and Times in Excel

In Excel, dates can be added, subtracted, and included in other calculations. To allow calculations to be performed on dates, Excel stores dates as sequential serial numbers, beginning with 1 - which is January 1, 1900 on a Windows PC and January 1, 1904 on a Macintosh. For example, on a Windows PC, the serial number for January 1, 2010 is 40179 because it is the 40,179^th day since day 1.

Any date may be entered into an Excel spreadsheet, but to have a serial number, the date must be in the range of January 1, 1900, to December 31, 9999, on a Windows PC; and from January 1, 1904, to December 31, 9999, on a Mac.

Microsoft recommends always entering a 4-digit year to ensure accuracy when dates are used in calculations, but you should be safe using 2-digit years if the years are relatively current. Excel assumes a 21st century date (20xx) for year values of 00 through 29, and a 20th century date (19xx) for year values 30 through 99. These are operating system defaults and can be customized on most systems.

Below is an example of how dates can be used in a calculation. Cell A1 contains 12/31/2011 and cell B1 contains 12/02/2011:

Although Excel stores serial numbers, how a date displays depends on a cell's format. If a cell has not been preformatted, Excel displays the date according to the "Short date" format of the Windows operating system. To change the Short Date format, click Start, click Control Panel, and click the "Regional Settings" or "Region and Language" icon. Find the section where dates are formatted and select a new format. Click the "Customize" button on some versions of the Windows OS in order to select a different format.

To see the actual serial number of a date, format the cell with the category of "Number" with no decimal points.

TODAY

When the TODAY function is placed in a cell, Excel returns the current date. The TODAY function is typically added to a worksheet to identify the date the worksheet was created or last updated on a printed copy of the sheet. The syntax of the TODAY function is:

TODAY()

How the date displays depends on the cell's format. You may format the cell if you don't like the default date format, or if the cell has been preformatted to display a number, currency, etc.

To format a cell, right-click in the cell, click Format, and click the Number tab. Select the category of Date, and choose a date format from the list in the right column. In Excel 2007 and 2010, you may view the "Format Cells" window quickly by selecting the cell, and then clicking the small arrow in the lower right corner of the Number group on the Home tab.

NOW

When the NOW function is placed in a cell, Excel returns the serial number of the current date and time. The only difference between the TODAY and NOW functions is that the NOW function includes the time as well as the date. The NOW function is typically added to a worksheet to identify the date and time a worksheet was created or updated on a printed copy of the sheet. The syntax of the NOW function is:

NOW()

How the date and time display depends on the cell's format. You may format the cell if you don't like the default date/time format, or if the cell has been preformatted to display a number, currency, etc.

To format a cell, right-click in the cell, click Format, and click the Number tab. Select the category of Date, and choose a date and time format from the list in the right column. In Excel 2007 and 2010, you may view the "Format Cells" window quickly by selecting the cell, and then clicking the small arrow in the lower right corner of the Number group on the Home tab.

Back to Top

DATEVALUE

The DATEVALUE function converts a date represented by text to a serial number. The syntax of the DATEVALUE function is:

DATEVALUE("date-text")

… where date-text is a date written in one of the Microsoft Excel date formats (e.g. "8/23/2011" or "23-Aug-2011"). The date must be enclosed in double quotation marks. If the year portion is omitted, Excel defaults to the current year. The date-text must be an actual date, or reference to a cell that contains a date in text format.

The DATEVALUE function is useful when you need the serial number of a date for a function such as NETWORKDAYS (see tutorial). Just use the DATEVALUE function in a cell that has not been preformatted and Excel will display the date's serial number. This is faster than typing a date into a cell and then formatting the cell with a category of Number.

Don't be confused between the value of a cell, and how Excel displays the contents of a cell. Regardless of how a cell is formatted, the value that Excel stores when a date is entered into a worksheet cell is a serial number.

In Conclusion

Consider locking any cell containing a function or formula, and protecting the worksheet, to prevent accidently deleting formulas and functions. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.

We hope this article has been helpful. Cheers!

   Back to Top