Use Today's Date within Worksheet Calculations in Excel

Calendar date
Image Source / Getty Images

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

Information in this article applies to Excel 2019, 2016, 2013, 2010, 2007; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

TODAY Function Syntax and Arguments

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 to enter the TODAY function into an Excel worksheet:

  • Type the complete function into a worksheet cell.
  • Enter the function using the TODAY function dialog box.

Since the TODAY function does not have any arguments that can be entered manually, it's just as easy to type the function as it is to use the dialog box.

Each time a worksheet is opened, the date changes unless automatic recalculation is turned off. To prevent the date from changing each time a worksheet using automatic recalculation is opened, use a keyboard shortcut to enter the current date.

Use TODAY in Excel Calculations

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

In the image below, rows 3 to 5 extract information related to the current date (such as the current year, month, or day) using the output of the TODAY function in cell A2 as the argument for the YEAR, MONTH, and DAY functions.

Screenshot of Excel showing use of the TODAY function

The TODAY function also calculates the interval between two dates, such as the number of days or years. See rows 6 and 7 of the image above.

Dates as Numbers

The dates in the formulas in rows 6 and 7 can be subtracted from each other because Excel stores dates as numbers. These number are formatted as dates in the worksheet to make them easier 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). 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 = 1113.

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

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 = 19.

Cell A7 was formatted as General before the formula was entered and shows an incorrect result. To fix this problem, see the Fix Date Format Issues section at the end of this article.

Troubleshoot Date Recalculation Problems

If the TODAY function does not update to the current date each time the worksheet is opened, automatic recalculation for the workbook has been turned off.

To activate automatic recalculation:

  1. Select File > Options. On a Mac, select Excel > Preferences.

  2. Select Formulas. On a Mac, select Calculation.

  3. In the Calculation Options section, select Automatic to turn on automatic recalculation.

    A screenshot showing how to enable automatic workbook calculations in Excel
  4. Close the dialog box and return to the worksheet.

Fix Date Format Issues

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. Cell A7 in the example shows a cell that has been formatted as a date. It contains the wrong information. 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 or cells with the incorrect formatting.

  2. Right-click on the highlighted cells to open the context menu.

  3. Select Format Cells to open the Format Cells dialog box.

  4. Select the Number tab to display the formatting options.

  5. In the Category section, choose General.

    A screenshot showing how to change the number format in Excel
  6. Select OK to close the dialog box and return to the worksheet.