Use Today's Date within Worksheet Calculations in Excel

Calendar date
Image Source / Getty Images

The TODAY function can be used to add the current date to a worksheet and in date calculations. The function is one of Excel's volatile functions, which means that it usually updates itself every time a worksheet containing the function is recalculated.

Normally, worksheets recalculate each time they are opened, so that every day the worksheet is opened the date will change unless automatic recalculation is turned off. To prevent having the date change each time a worksheet using automatic recalculation is opened, use a keyboard shortcut to enter the current date instead.

TODAY Function Syntax and Arguments

Screenshot of Excel showing use of the TODAY function

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments. The syntax for the TODAY function is:

=TODAY( )

TODAY uses the computer's serial date, which stores the current date and time as a number, as an argument; it obtains this information on the current date by reading the computer's clock.

There are two options for entering the TODAY function into your Excel worksheet, they include:

  1. Typing the complete function into a worksheet cell.
  2. Entering the function using the TODAY function dialog box.
  3. Since the TODAY function does not have any arguments that can be entered manually, many people opt to just type in the function rather than use the dialog box.

Using TODAY in Excel Calculations

Screenshot of Excel showing using the TODAY function with DATE

The true usefulness of the TODAY function becomes evident when it is used in date calculations, often in conjunction with other Excel date functions.

In this article's sample images, rows three to five extract information related to the current date, such as the current year, month, or day, by using the output of the TODAY function in cell A2 as the argument for the YEAR, MONTH, and DAY functions.

The TODAY function can also be used to calculate the interval between two dates, such as the number of days or years as shown in rows six and seven in the image above.

Dates as Numbers

The dates in the formulas in rows six and seven can be subtracted from each other because Excel stores dates as numbers, which are formatted as dates in the worksheet to make them easier for us to use and understand.

For example, the date 11/1/2018 (November 1, 2018) in cell A2 has a serial number of 43405 (the number of days since January 1, 1900) while October 15, 2015 has a serial number of 42,292.

The subtraction formula in cell A6 makes use of these numbers to find the number of days between the two dates:

43,405 - 42,292 = 344

In the formula in cell A6, Excel's DATE function is used to ensure that the date 10/15/2015 is entered and stored as a date value.

In the example in cell A7 uses the YEAR function to extract the current year from the TODAY function in cell A2 and then subtracts from that 1999 to find the difference between the two years:

2018 - 1999 = 16

Troubleshooting Date Problems

Screenshot of Excel showing Automatic Calculations

If the Current Date Is Not Updating

As mentioned, if the TODAY function does not update to the current date each time the worksheet is opened, it is likely that automatic recalculation for the workbook has been turned off.

To activate automatic recalculation:

  1. Click on the File menu then select Options (Mac users will find the same option under Excel > Preferences).
  2. Click on the Calculation option in the window to view the available options.
  3. Under the Calculation Options section, click on Automatic to turn on automatic recalculation.
  4. Close the dialog box and return to the worksheet.

Subtracting Dates Formatting Issue

When subtracting two dates in Excel, the result is often displayed as another date rather than a number; this happens if the cell containing the formula was formatted as General before the formula was entered. Because the formula contains dates, Excel changes the cell format to Date.

To view the formula result as a number, the cell's format must be set back to General or to Number:

  1. Highlight the cell(s) with the incorrect formatting.
  2. Right-click with the mouse to open the context menu.
  3. In the menu, select Format Cells to open the Format Cells dialog box.
  4. In the dialog box, click on the Number tab to display the formatting options.
  5. Under the Category section, click on General.
  6. Click OK to close the dialog box and return to the worksheet.