3 Quick Methods to Insert Current Date and Time into Your Excel Worksheet

When you are working on your Excel file, you will probably have a need to note down the current date and time. Therefore, here we will discuss about 3 methods to achieve this task.

Knowing when you have modified a file is very important for you to track changes. Thus, sometimes you will insert the current date and time into a worksheet to make a mark. Instead of manually inserting date and time into cells, you can also use the three methods in this article. And in the coming passage, we will discuss about the usage in detail.

Method 1: Use Shortcut Keys Combos

In the first method, you will need to use shortcut keys combos. And for different date and time format, there are different combos.

  1. Click the target cell where you need to input the date and time.
  2. Now press the shortcut keys “Ctrl +;” on the keyboard. And then you will see the date in the worksheet.Date

On the other hand, if you need to input the time into the worksheet, the shortcut keys should be “Ctrl +Shift + ;” on the keyboard.

Besides, if you want to show both date and time in a cell, you can also use them for twice.Other Shortcut Keys

Therefore, in your worksheet, you can apply those shortcut keys combos according to your need.

Method 2: Use Functions

Excel also has functions for date and time. Now let’s see how those functions take effect.

  1. Click a cell that you need to show the time or the date.
  2. And then input this formula into the cell:

=NOW()

  1. Next press the button “Enter” on the keyboard. The current date and time will appear in this cell.NOW Function

On the other hand, if you only need to show the date of today, you can input this formula:

=TODAY()

This time, only the date will appear in this cell.

When you need to input the time in a cell, you need to input another formula:

=HOUR(NOW()) & “:” & MINUTE(NOW())

Next press the button “Enter” on the keyboard. Therefore, only the time will appear in the target cell.Only Time

You may also have a try in your own worksheet.

However, there exists another problem when you use the time functions. The next time you open the workbook, you will find that the date and time will refresh. Therefore, you cannot know the exact time that you last modified. To solve this problem, you can follow the two tips below.

Tip 1: Close the Auto Calculation

  1. Click “File” in the ribbon.
  2. And then click “Options”.Click File and Click Option
  3. In the “Excel Options” window, click the option “Formula”.
  4. Next check the option “Manual”.
  5. After that, uncheck the sub option of the “Manual”.Manual
  6. Now click the button “OK” in the window to save the settings.

And the next time, the date and time will not refresh automatically unless you manually recalculate in this file or this worksheet. But changing his feature may affect other formulas in this file.

Tip 2: Copy to other Cells

Closing the auto calculate feature will also affect other formulas in Excel. Thus, you can copy the result of those formulas into other cells.

  1. Select the target cell that you need to copy.
  2. And then press the shortcut keys “Ctrl + C” on the keyboard to copy the cell.
  3. Now right click the target cell that you need.
  4. In the pop-up menu, choose the option “Paste Special”.Paste Special
  5. In the “Paste Special” window, choose the option “Values and number formats”.Option
  6. Next click the button “OK”.
  7. After that, press the button “Esc” to disable the copy mode of the cell.

In this target cell, there will be no formula. Thus, you don’t need to worry about the auto calculation.No Formula

You can use either of these two tips according to your actual need.

Method 3: Use Excel Macros

Except for the above two methods, you can also use macros to finish this task.

  1. Press the button “Alt + F11” on the keyboard to open the Visual Basic editor.
  2. And then click the tab “Insert” in the toolbar.
  3. Next choose the option “Module” in the drop-down menu.Insert Module
  4. In this step, copy the VBA codes into the new module:
Sub InsertDate()
  Range("E2").Select
  With Selection
    .Value = "=Today()"
    .Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  End With
  Application.CutCopyMode = False
End Sub

Here we will insert the date into the cell E2. And you can also modify it according to your actual need. Besides, if you want to show the time and date, change the “=Today()” into “=Now()”. And if you need to show the time only, change it into “=Hour(Now())&””:””&Minute(Now())”.

  1. And then click the button “Run Sub” in the toolbar or press the button “F5”on the keyboard to run this macro.
  2. Now come back to the worksheet and see the result.

Although there is a function in the VBA codes, the result in cell will not contain formula.VBA result

You can see that using VBA to finish this task is also very easy.

A Comparison between the Three Methods

Here we have analyzed the advantages and disadvantages of the above three methods. You can refer to the table below:

Comparison

Shortcut keys Combos Functions

Excel Macros

Advantage

Using the shortcut keys combos can help you quickly insert the date and time into cells. Compared with VBA macros, the time and date function are very easy to use in Excel. With just one click, you can get the result in the target cells.

Disadvantage

You may be confused about the two similar with the different combinations. Hence, you need to remember their usage. The function will recalculate automatically, and this will cause trouble to you. Thus, you need to have additional settings. If you are not familiar with VBA codes, using this method will make things more complicated.

Actually, those three methods are very useful. In your actual work, you need to choose one according to your own need.

Hackers are Spying on Your Precious Data

In your Excel files, you will certainly store many precious data and information. This leads to the result that hackers are spying on those files. And probably one day you will find that you cannot get access to those files. This condition can also be regarded as Excel corruption. To repair Excel xlsx corruption and get access again, you can use a third party tool. Many users have used this tool, and they all get back their data and information easily. Hence, this tool is reliable.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word file problem and outlook repair software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published. Required fields are marked *