Time & Date

Applies to: Excel for Office 365, Excel for Office 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011.

Adding the present date and time is useful for creating a record of activities or keeping a track record of recalculated formulas in a cell. There are various ways of inputting the current date and time into a cell.

Windows

Adding a static time or date into an Excel cell

A static value in a spreadsheet is merely one that remains the same when the worksheet is revised or opened.

  1. In a worksheet, choose whichever cell you want to insert the present time or date.
  1. Do any of the following:
  • For inserting the current date, press Ctrl+; (semi-colon).
  • To add the current time, press Ctrl+Shift+; (semi-colon).
  • To input the current time and date, press Ctrl+; (semi-colon), followed by pressing Space, and then press Ctrl+Shift+; (semi-colon).

Insert a time or date with an updated value

A date or time that changes when the worksheet is revised or the workbook is accessed is known as being “dynamic” rather than static. Utilising the worksheet function in a worksheet is the easiest way to retrieve a dynamic time or date in a cell.

To add the current time or date in a changeable format, apply the TODAY and NOW functions, as seen in the example below.

  • The TODAY function is helpful for calculating intervals and when you specifically need the current date on a worksheet, no matter when a spreadsheet is opened.

= YEAR( TODAY() – 2000

This formula applies the TODAY function as an argument for the YEAR function to get the current year, and then subtracts 2000, giving the individual’s age of 19.

=NOW()

This formula simply returns the current date and time.

  1. Choose the TODAY and NOW functions shown above, and next press Ctrl+C.
  1. In the new worksheet, select cell A1 once before pressing Ctrl+V. For working in Excel online, simply repeat copying and pasting for each cell in the example.

Important: For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To alternate between seeing the results and reading the formulas that yield the results, press Ctrl+`(grave accent), or on the Formulas Tab, in the Formula Auditing section, select the Show Formulas option.

When you copy the example to a new worksheet, you can tailor it to match your goals.

Note: The results of the TODAY and NOW functions change only when the worksheet is calculated or when a macro that contains the function is run. Cells that contain these functions are not updated continuously. The date and time that are used are taken from the computer’s system clock.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Leave a Reply

%d bloggers like this: