Use Today's Date in Worksheet Calculations in Excel

01
of 01

Excel TODAY Function

Using the Excel TODAY Function in Date Calculations
Using the Excel TODAY Function in Date Calculations. © Ted French

The TODAY function can be used  adding the current date to a worksheet as shown in row two of the image above and in date calculations -- rows three to seven above.

The function, however, 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 every day that 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, try using this keyboard shortcut to enter the current date instead.

The TODAY Function's 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( )

The function does not have any arguments that can be set manually. 

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.

Entering the Current Date With the TODAY Function

Options for entering the TODAY function include:

  1. Typing the complete function: =TODAY() into a worksheet cell
  2. Entering the function using the TODAY function dialog box

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.

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 tab of the ribbon to open the file menu
  2. Click on Options in the menu to open the Excel Options dialog box
  3. Click on the Formulas option in the left hand window to view the available options in the right hand window of the dialog box.
  4. Under the Workbook Calculation options section, click on Automatic to turn on automatic recalculation
  5. Click OK to close the dialog box and return to the worksheet

Using TODAY in Date Calculations

The true usefulness of the TODAY function becomes evident when it is used in date calculations - often in conjunction with other Excel date functions  - as shown in rows three to five in the image above.

The examples in 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 9/23/2016 (September 23, 2016) in cell A2 has a serial number of 42636 (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:

42,636 - 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 (2016) from the TODAY function in cell A2 and then subtracts from that 1999 to find the difference between the two years:

2016 - 1999 = 16

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.

To do this:

  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 if necessary to display the Number formatting options;
  5. Under the Category section, click on General;
  6. Click OK to close the dialog box and return to the worksheet;
  7. The formula results should now be displayed as a number.
Format
mla apa chicago
Your Citation
French, Ted. "Use Today's Date in Worksheet Calculations in Excel." ThoughtCo, Jun. 4, 2017, thoughtco.com/use-todays-date-in-worksheet-calculations-4092305. French, Ted. (2017, June 4). Use Today's Date in Worksheet Calculations in Excel. Retrieved from https://www.thoughtco.com/use-todays-date-in-worksheet-calculations-4092305 French, Ted. "Use Today's Date in Worksheet Calculations in Excel." ThoughtCo. https://www.thoughtco.com/use-todays-date-in-worksheet-calculations-4092305 (accessed January 16, 2018).