How to Insert Current Date and Time in Excel (5 Easy Methods)

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

Use Shortcut Key to Insert Current Date and Time

  • Now, press Ctrl + ; (Semicolon). As a result, the current date will be shown like the figure below.

Use Shortcut Key to Insert Current Date and Time

  • Now, press the Spacebar key and again type Ctrl + Shift + ; (Semicolon). Consequently, the current time will be inserted like the figure below.

Use Shortcut Key to Insert Current Date and Time

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()

Apply NOW Function to Insert Current Date and Time

  • Press Enter and you will see the current time and date in the cell.

Apply NOW Function to Insert Current Date and Time

  • 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.

Run a VBA Code to Insert Current Date and Time

  • 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.

Run a VBA Code to Insert Current Date and Time

Read More: How to Add Hours, Minutes, and Seconds in Excel


Similar Readings


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.

Insert a Custom Function to Insert Current Date and Time

  • 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)

Insert a Custom Function to Insert Current Date and Time

  • 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.

Insert a Custom Function to Insert Current Date and Time

  • 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.

Insert a Custom Function to Insert Current Date and Time

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.

Use Power Query to Insert Current Date and Time

  • 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.

Use Power Query to Insert Current Date and Time

  • As a result, you will see a window like this below.

  • Now, go back to Home tab and Click on Close & Load To…

Use Power Query to Insert Current Date and Time

  • Now, select the following option like in the figure, and click OK.

Use Power Query to Insert Current Date and Time

  • The result will be like this below.

Use Power Query to Insert Current Date and Time

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

Use Power Query to Insert Current Date and Time

  • Now you will see the result like this.

Use Power Query to Insert Current Date and Time

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.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places. Currently, I am working as an Excel and VBA Content Developer. I try to present solutions of problems that are faced in excel in an easy and straightforward manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo