Insert current date and time quickly

Excel 2016
If you need to mark a date or time in the worksheet, Excel provides two shortcuts to do it:

Current date: Ctrl+; (semicolon)

Current time: Ctrl+Shift+; (semicolon)

Excel adds a static value when using these hotkeys to enter a date or time into the worksheet. In other words, the entered date or time does not change when the spreadsheet is recalculated. This is probably what you want in most cases, but you should be aware of this limitation.

The date and time are taken from your computer's system time. If the date or time in Excel is incorrect, use the Windows Control Panel to adjust your local date and time (see How to open Control Panel in Windows 10).

If you want the date or time display to be updated, use one of the following formulas:

= TODAY() - Returns the serial number of today's date.

= NOW() - Returns the serial number of the current date and time.

Notes:

  1. Dates in Excel are stored as integers representing the number of days since January 1, 1900 (or January 2, 1904, depending on the selected date system – see more about 1904 date system). The start date (January 1, 1900, or January 2, 1904) is stored as 1, and December 31, 9999 is stored as 2958465.

    For example, January 1, 2020, is stored as 43831 (in the 1900 date system).

  2. Times in Excel are stored as decimals, between .0 and .99999, which represent a proportion of the day, where .0 is 00:00:00 and .99999 is 23:59:59.

    For example, 0.25 is 12:00 PM.

  3. When you create a date with time in Excel, it is stored as a number with integer and decimal points, where integer keeps date and decimal keeps time.

    For example, 43875.46875 is February 14, 2020, 11:15 AM.


To change the format of date or time, do one of the following:

  • Change the format in the system (see How to change numbers and currency format in Windows 10).
  • Change the format of the selected cells:

       1.   Do one of the following:

    • Right-click on the selection and choose Format Cells... in the popup menu:
      Format cells in popup menu Excel 365
    • On the Home tab, in the Number group, click the dialog box launcher:
      Format cells in Excel 365
    • Press Ctrl+1.

       2.   In the Format Cells dialog box, on the Number tab, in the Category list, select the Date or Time item:

    Date formats in Excel 365

    Notes:

    1. Excel's short and long date formats are taken from your Windows Regional settings. These default formats are marked with an asterisk (*).
    2. You can choose any other locale from the Locale (location) dropdown list to choose predefined formats for that region.
    3. If none of the predefined formats meets your needs, you can create a custom number format.

Note: When you enter some data or create a formula, Excel can automatically format the cell as a date or a time. Often, this automation is helpful; other times, not. To return the number formatting to the default General format, do one of the following:

  • Select the General from the Number Format list, on the Home tab, in the Number group.
  • Press Ctrl+Shift+~ (tilde).

See also this tip in French: Comment insérer rapidement la date et l'heure actuelles.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.