How to Enter Date and Time in Excel (8 Quick Methods)

It is very helpful to insert the date and time in an Excel spreadsheet for the person who works as a financial accountant. We can add the date & time to keep track of actions when necessary. In this article, we will learn how to enter date and time in Excel with some quick methods. Let’s see the methods below.

Download the practice workbook from here.

8 Quick Methods to Enter Date and Time in Excel

This tutorial will guide you with 8 quick methods to enter the date & time in Excel easily. For explaining the methods we have used some excellent examples. Here, we have used different kinds of methods for entering date & time like keyboard shortcuts, Excel functions, VBA, Power Query tool, Power Pivot, etc. So, without further delay, let’s get started.

1. Keyboard Shortcuts to Enter Current Date & Time in Excel

It is quicker and more effective to enter the date and time using keyboard shortcuts in Excel if you need to do it for a few cells. Using the keyboard shortcuts is a static method for adding the date & time in Excel. Suppose, we have a dataset (B4:C5) in Excel where we need to enter Date in cell B5 and Time in cell C5. The steps are below.

Steps:

• First, to enter the date, select cell B5.
• Now, hold the Ctrl (control) key on the keyboard.
• While holding down the Ctrl key, press the : (colon) key.

• Next, we want to insert the time.
• To do so, first of all, go to cell C5.
• Then, holding down the Ctrl & Shift key, press the ‘:’ key on the keyboard.
• Finally, you will see the Date & Time in cells B5 & C5 respectively.

2. Apply Excel TODAY Function to Insert Current Date

Let’s say, we have a dataset (B4:C5) in Excel. Here, we will use the TODAY function in Excel to insert the current date in cell B5. The steps to do so are below.

Steps:

• To begin, select cell B5.
• Now, to insert the current date enter the following formula in cell B5:

• Afterward, press the Enter key.
• Finally, see the result in cell B5.

3. Insert Both Date & Time in Excel Using NOW Function

Here, we will use the NOW function to add both Date & Time in Excel. However, we will apply this function in cell C5 of the dataset (B4:C5) below. To do so, follow the steps below.

Steps:

• Firstly, go to cell C5.
• Now, to get the current date & time, type the formula in this cell (C5):

• Finally, press the Enter button to get the current date & time.
• See the result in cell C5.

4. Automatically Enter Date & Time with Circular References Trick in Excel

Assuming, we have a dataset (B4:C7) in Excel where we need to enter the current Dates & Times in the range C5:C7. Here, we will use the circular references trick for inserting the Dates & Times. That means we will enter the data in the range B5:B7 and get the corresponding Dates & Times in the range C5:C7. Let’s see the steps below to apply this method.

Steps:

• In the beginning, go to the File tab.

• Then, click on Options.

• In turn, the Excel Options window will appear.
• Next, select Formulas from the left sidebar of the window.

• After that, go to the Calculation options section.
• Subsequently, put a tick mark in the Enable iterative calculation box.
• Click OK.

• Thereupon, select cell C5.
• However, to apply the circular references trick, type the formula below with the IF function and the NOW function:

• Accordingly, press the Enter key.
• Afterward, drag the fill handle to copy the formula to cell C7.

• Later, to get the dates & times in the desired formatting, first, select the range B5:C7.
• Secondly, right-click on the selection.
• Thirdly, click on Format Cells.

• As a result, the Format Cells dialog box will pop up.
• At this time, go to the Number tab > Category > Date > Type > select the type you want > OK.

• Eventually, select cell B5 and enter any data (Data 1).

• After pressing the Enter key, you will find the corresponding current date & time in cell C5.

• Similarly, when you input any data in cell B6, you will get the date & time of inserting the data.

• In this way, you can enter the current dates & times by inserting any data using the circular references trick (see screenshot).

5. Excel VBA for Entering Date & Time Automatically

We can also use the  VBA code to insert the current Dates & Times automatically in Excel. To apply the Excel VBA, you need to use the Developer tab in the ribbon section of an Excel worksheet. To illustrate this method, we will use the same dataset that was used for method 4. The steps to execute this technique are below.

Steps:

• First of all, right-click on the desired worksheet tab (VBA) located in the bottom of the Excel sheet.
• Next, click on View Code.

• Hence, the VBA code window will open.
• Now, enter the following VBA code in the window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aInt As Integer
Dim aStr As String
Dim bStr As String
On Error Resume Next
aStr = "B"
abStr = "C"
If (Not Application.Intersect(Me.Range(aStr & ":" & aStr), Target) Is Nothing) Then
aInt = Target.Row
Me.Range(abStr & aInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss")
End If
End Sub

• Subsequently, go to the Run tab and click on Run Sub/Userform.

• In turn, a window named Macros will appear.
• Then, type any name (VBA) in the Macro Name box.
• Click on Create.

• Again, go to Run > Run Sub/UserForm.

• At this time, return to the worksheet (VBA) and select cell B5.
• After entering any data (Data 1) in cell B5, you will get the corresponding date & time in cell C5.

• In the same way, you can enter the current dates & times in cells C6 & C7 by entering the data in cells B6 & B7 (see screenshot).

6. Enter Date & Time in Excel by Forming a Custom Function

In this approach, we will form a custom function for entering Dates & Times in Excel. We need to use the VBA code again to create the custom function. Here, the name of our custom function will be Timestamp. We will again use the same dataset of method 4 for this method. The steps to build this custom function are below.

Steps:

• Firstly, right-click on the sheet tab (Custom Function) > View Code.

• Consequently, the VBA code window will open.
• Now, select the desired sheet for inserting the code.
• Accordingly, right-click on the selected sheet (Sheet6) > Insert > Module.

• Therefore, insert the following VBA code in the blank VBA code window.

Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd-mm-yyy hh:mm:ss")
Else
Timestamp = ""
End If
End Function

• Subsequently, press Alt + F11 to return to the worksheet.
• Eventually, to insert the custom function, select cell C5 > type the following formula:

• Press the Enter key.
• However, drag the fill handle to copy the formula up to the desired cell (C7).

• Thereupon, to get the date & time, input any data (Data 1) in cell B5.

• After pressing the Enter key, you will find the date & time in cell C5.

• Similarly, you will get the date & time of cell C6 after entering the data in cell B6.

• In the same manner, you can also find the date & time in cell C7.

7. Get Date & Time Using Power Query Tool

Assuming, we have a dataset (B4:B8) in excel which contains the Names of some students. In this method, we will use the Power Query tool in Excel to insert the entry dates & times of the students. We can find the Power Query tool in the Data tab of an Excel worksheet. Let’s see the steps below to utilize this tool for inserting the dates & times.

Steps:

• First, we need to transform our dataset (B4:B8) into an Excel Table.
• For this, select the range B4:B8 > Data tab > Get & Transform Data group > From Table/Range.

• In turn, a Create Table window will pop up.
• Next, put a tick mark in the My table has headers box.
• Click OK.

• Consequently, the Power Query sheet will appear.
• Now, we need to add a new column for inserting the dates & times.
• To do so, go to the Add Column tab > Custom Column.

• Hence, the Custom Column window will appear.
• Thereupon, select the box of the New column name and enter any name (Time of Entry) as you wish.
• After that, to get the date & time, assign the following formula in the Custom column formula box:

• After inserting the formula, click on OK.

• As a result, a new column named Time of Entry will be added beside the Name column with the entry dates & times for the students.

• Moreover, you can extract the dates from this new added column.
• For this, select the Time of Entry column > Add Column tab > Date > Date Only.

• Hence, a new column named Date will be added with the dates only.

• For extracting the times from the Time of Entry column, select the entire Time of Entry column > Add Column tab > Time > Time Only.

• Thus, a new column named Time will be added containing only the times.

8. Enter Date & Time in Excel with Power Pivot

Here, we will utilize the Power Pivot tab in Excel for entering dates & times. Usually, the Power Pivot tab is not available in the ribbon section of an Excel worksheet. So, you need to add it to the ribbon section first. In this method, we will use the dataset (B4:B8) below that contains the Names of some students. Now, we will use the Excel Power Pivot feature to insert the entry dates & times of the students. The steps to do so are below.

Steps:

• To begin, go to the Power Pivot tab.
• Next, click on Add to Data Model.

• In turn, the Create Table window will appear.
• After that, check the My table has headers box.
• Click OK.

• Consequently, the Power Pivot for Excel sheet will open with a newly added column named Add Column.

• Therefore, select the first cell of the Add to Column.
• Now, to find the dates & times, type the formula below:

• Finally, press the Enter key to get the dates & times.
• See the final result in the screenshot below.

How to Change the Format of Date & Time in Excel

We can also change the format of the added date & time. For this, we will use the dataset (B4:C5) below that contains a date in cell B5 that was added using the TODAY function. Again, the dataset contains the current date & time in cell C5 added with the Now function. Follow the steps below to change the format of them.

Steps:

• First of all, we want to change the format of the date in cell B5.
• Hence, right-click on cell B5 > select Format Cells.

• As a result, the Format Cells dialog box will open.
• Therefore, Number tab > Category > Date > Locale (location) dropdown > English (United States) > Type > select any type from the list > OK.

• Finally, you will get your desired formatting of the date in cell B5.

• However, to extract the time from the C5 cell, select cell C5 > right-click on it > click on Format Cells > Number > Category > Time > Type > select any type > OK.

How to Enter Date & Time in Excel While Printing

Suppose, we need to insert the date & time in the excel worksheet below while printing. This worksheet has a dataset (B4:D7) containing the Names, Subjects & Marks of some students. In this approach, we will demonstrate the process to enter the current date & time as a Footer at the time of printing the worksheet. The steps to do so are below.

Steps:

• First of all, go to the View tab > Workbook Views group > Page Layout.

• In turn, the Add footer sections will appear in the lower part of the worksheet.
• Now, keep your cursor in the middle section.

• After that, a new tab named Header & Footer will appear in the ribbon section.
• Consequently, go to the Header & Footer tab > Header & Footer Elements group > Current Date.

• Hence, you will see &[Date] in the footer section.
• Eventually, to enter the time, press the spacebar on the keyboard.

• Therefore, to insert the time go to the Header & Footer tab > Header & Footer Elements group > Current Time.

• Thus, the time will be added to the footer section.

• Lastly, after clicking outside the footer section, you will find the current date and time in the section.
• See the result in the screenshot below.

Conclusion

I hope this article will be helpful for you to enter the date and time in excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.