Aniruddah Alam
In many situations, we may need to input the current date and time frequently for instance keeping a date and time log of sales in a shop, entry and exit time of employees in an office, etc. In this article, I will show some easy techniques on how to insert current date and time in excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Useful Methods to Insert Current Date and Time in Excel
In this section, we will demonstrate 5 effective methods to insert time and date in excel with appropriate illustrations. Excel has two ways of storing data and time. One is the dynamic way, and the other one is the static way. In a static way, the date and time don’t get changed with the time. But in a dynamic way, the stored date and time are updated each time when any calculation is performed or the worksheet is reopened. Hence, based on the type we choose, we will have to use one of the methods below to insert current data and time in excel.
1. Use Shortcut Key to Insert Current Date and Time
In this method, the date and time will be stored statically which means that the value will remain unchanged. To do this follow the steps below.
Steps:
- Select a cell where you want to put the current date and time. Here, we have selected cell
- Now, press Ctrl + ; (Semicolon). As a result, the current date will be shown like the figure below.
- Now, press the Spacebar key and again type Ctrl + Shift + ; (Semicolon). Consequently, the current time will be inserted like the figure below.
Read More: How to Add Time to Date in Excel (4 Useful Methods)
2. Apply NOW Function to Insert Current Date and Time
If you want your input date and time to be changed dynamically then you may use the NOW function. Follow the steps below.
Steps:
- Select a cell in your workbook where you want to input the current date and time. Below in the figure, we have selected cell
- Then write down the following formula in the cell.
=NOW()
- Press Enter and you will see the current time and date in the cell.
- Remember, if you perform any operation on the workbook, the date and time will be updated automatically from the system time.
Read More: How to Add Time in Excel Automatically (5 Easy Ways)
3. Run a VBA Code to Insert Current Date and Time
Sometimes we may face a situation where if data is inserted in a cell, its adjacent cell will automatically store the time and date of entry. For instance, look at the sheet below.
In the above figure, if we insert anything in a cell under the Task column, the adjacent cell under Completion Date & Time should store the date and time of entry. In this situation, we can run the VBA code given below. To do that, follow the steps.
Steps:
- Click on the Developer Tab, and then click on Visual Basic to open VBA Project window. Alternatively, you can also press Alt+F11.
- A new window should open like this below.
- Now, paste the following code into the window.
'Code by Aniruddah
Private Sub Worksheet_Change(ByVal Tgt As Range)
On Error GoTo hdlr
If Tgt.Column = 2 And Tgt.Value <> "" Then
Application.EnableEvents = False
Tgt.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
Application.EnableEvents = True
End If
hdlr:
End Sub
- Then close the VB editor.
- Now, if you insert something in a cell of column B, the adjacent cell of column C will store the insertion time and date like this below.
Read More: How to Add Hours, Minutes, and Seconds in Excel
Similar Readings
- Add Milliseconds to Time in Excel (with Quick Steps)
- How to Add 30 Minutes to Time in Excel (3 Easy Methods)
- Add Hours to Time in Excel (8 Quick Ways)
- How to Add Minutes to Time in Excel (5 Easy Ways)
4. Insert a Custom Function to Insert Current Date and Time
We can also create a custom function to accomplish the task described in the previous method. To do that, follow the steps below.
Steps:
- Using the same steps of method 3, open the VB editor.
- Now, in the VB editor window, click on the Insert tab, then click on Module.
- Now paste the code below into the window
Function Time_Date_stamp(Reference As Range)
If Reference.Value <> "" Then
Time_Date_stamp = Format(Now, "dd-mm-yyy hh:mm:ss")
Else
Time_Date_stamp = ""
End If
End Function
- Now close the window. By pasting this code, we have created a new function named This function returns the date and time of data entry of the cell which is given as the argument.
- Here in cell C5, input the following formula.
=Time_Date_stamp(B5)
- Now, press Enter key.
- Now to apply the formula to other cells, use the Fill Handler. Take your cursor to the bottom right corner of C5. When you see the Fill Handler icon (+), drag it down to C10.
- Now, if we input anything in the B5 cell. C5 will store the day and time of insertion.
- We will get the same result if we input value in the other cells as well.
Read More: Adding Hours and Minutes in Excel (4 Suitable Methods)
5. Use Power Query to Insert Current Date and Time
In this method, we will use the Power Query feature to insert the Current Date and Time. To do that, we have taken a sample table like this below.
- Now, go to the Data tab and from the Get & Transform Data group, select From Table/Range.
- Consequently, the Power Editor window will open up. In the window, from Add Column tab, select Custom Column.
- In a new window, Write down the New Column Name as Date&Time and Custom column formula as shown in the figure below.
=DateTime.LocalNow()- Now, click OK.
- As a result, you will see a window like this below.
- Now, go back to Home tab and Click on Close & Load To…
- Now, select the following option like in the figure, and click OK.
- The result will be like this below.
Here, the cells are not formatted, hence we need to format the Date&Time column. To do that, go to the Number group and click on the arrow shown in the figure.
- Now click on the More Number Formats.
- Here, in the Custom tab, select the following format shown in the figure and click
- Now you will see the result like this.
Read More: How to Add Time in Excel Over 24 Hours (4 ways)
Things to Remember
- Use the 2nd method to input dynamic Date and Time. The rest of the method is for inputting static Date and Time
- Use methods 3 and 4 if you need to input the date and time automatically
- Power Query is very lengthy. method, hence one should prefer other methods over Power Query.
Conclusion
That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit ExelDemy for more exciting articles on Excel.
