Date Functions

Dates and times are stored by Microsoft Excel as a serial number and converted to date and/or time when you set the format (display) of a cell to date or time. There are several date/time functions. Two useful ones are:

TODAY

• returns the serial number of a particular date
• format is =TODAY() with no argument
• if entered into a cell, the cell will display the current date
• to set the format of the date, use FormatCells and use the [Date] category

NOW

• returns the serial number of the current date and time
• format is =NOW() with no argument
• if entered into a cell, the cell will display the current date and time
• to set the format of the date and time, use FormatCells and use the [Date] category

Since dates and times are stored as numbers, this permits “date arithmetic” to determine how many days (or how much time) has elapsed from one date (or time) to another by including these functions in a formula. For example, how old are you? If you entered your birth date in one cell, then used one of these functions to return the current date in another, you can construct a very simple formula to do “date arithmetic” to calculate your age. Can you figure out how to do this? Can you also figure out how to calculate your age in days? months? years?

More About These Functions

Both the NOW and TODAY functions are used to report the current date and time as set by the computer's internal clock. In reality, the functions return a serial number. All dates in a computer are stored as a serial number with day 1 being January 1, 1904. Thus, the functions literally count the number of days and minutes since January 1, 1904; minutes are recorded as fractions of a whole day. The use of the functions is rather straightforward. They can be used as part of any calculation in a spreadsheet to return the current date and time. Neither takes an argument; they are simply entered as NOW() or TODAY(). The result, as just noted, in both cases is a serial number. To display the current date in date format, simply format the field data as a date as described elsewhere.

The purpose of either of these functions is rather straightforward, to return the current date and time. As such either function can be used as part of a calculation or simply to display in a spreadsheet file the current date and time. 

Formatting the Display of the NOW or TODAY Functions

What appears on the screen when you enter either function in a spreadsheet cell depends upon the format of the cell. Remember that the NOW function returns the number of days and minutes in decimal form that have elapsed since 12:00 am on January 1, 1904 while the TODAY function returns only the number of days in decimal form that have elapsed since January 1, 1904.

The result of either function will be displayed as a date, time or general number depending upon your choice of format!

To Summarize

1. The NOW function counts the number of days and minutes since  January 1, 1904
2. The TODAY functions counts the number of days only since January 1, 1904
3. If you enter =NOW() or =TODAY() in a spreadsheet cell and set the format to display a date only, the current date will be displayed
4. If you enter TRUNC(NOW()) in a formula, the result will be to calculate the number of days since January 1, 1904 only; the truncation results in the calculation ignoring the minutes in the day.
5. If you enter  in a formula, TRUNC(NOW())-x where x is a reference to a date in the past, the result is to calculate the number of days that have elapsed since that date in the past.

Go Back

Copyright©2003
last updated August 12, 2003