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.
- Click the target cell where you need to input the date and time.
- Now press the shortcut keys “Ctrl +;” on the keyboard. And then you will see the date in the worksheet.
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.
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.
- Click a cell that you need to show the time or the date.
- And then input this formula into the cell:
- Next press the button “Enter” on the keyboard. The current date and time will appear in this cell.
On the other hand, if you only need to show the date of today, you can input this formula:
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.
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
- Click “File” in the ribbon.
- And then click “Options”.
- In the “Excel Options” window, click the option “Formula”.
- Next check the option “Manual”.
- After that, uncheck the sub option of the “Manual”.
- 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.
- Select the target cell that you need to copy.
- And then press the shortcut keys “Ctrl + C” on the keyboard to copy the cell.
- Now right click the target cell that you need.
- In the pop-up menu, choose the option “Paste Special”.
- In the “Paste Special” window, choose the option “Values and number formats”.
- Next click the button “OK”.
- 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.
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.
- Press the button “Alt + F11” on the keyboard to open the Visual Basic editor.
- And then click the tab “Insert” in the toolbar.
- Next choose the option “Module” in the drop-down menu.
- 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())”.
- And then click the button “Run Sub” in the toolbar or press the button “F5”on the keyboard to run this macro.
- 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.
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:
|Shortcut keys Combos||Functions||
|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.|
|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.
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