How to Record Time of Data Entry in Excel (2 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

Excel provides us with a wide range of facilities to handle a large amount of data and work with them. When entering data into an Excel sheet, we might need the data entry time sometimes. In this article, I will show you 2 effective ways to record the time of data entry in Excel.


Record Time of Data Entry in Excel: 2 Effective Ways

1. Record Time of Data Entry by Merging IF and NOW Functions

Say you have an Excel file where the employees would enter their names when they get into the office. Through this, you want to know their entry time. You can merge IF and NOW functions to achieve this target.

๐Ÿ“Œ Steps:

  • First and foremost, click on cell C5 and insert the following formula.
=IF(B5<>"",IF(C5<>"",C5,NOW()),"")
  • Subsequently, hit the Enter key.

Formula to Record Time of Data Entry in Excel

  • Afterward, for all the other cells drag your Fill Handle below.

Use Fill Handle Feature to Copy Same Formula

  • Thus, your automated formula for record of data entry time is established.
  • Now, insert any employee name in cell B5.
  • Consequently, there will be the instant time of data entry stamped in cell C5 automatically.

Recorded Time of Data Entry in Excel Through Formula

  • As you have created a formula for all cells below, insert employee names in every cell from B6 to B9 to get all data entry time.

All Records of Data Entry Time in Excel

  • Now, the formula may sometimes show errors due to iteration problems.
  • To avoid this, go to the File tab.

Access the File Tab

  • Afterward, click on the Options option from the expanded File tab.

Options Option

  • As a result, the Excel Options window will appear.
  • Following, go to the Formula tab here >> choose the Automatic option from the Workbook Calculation options >> tick on Enable iterative calculation option >> click on the OK button.

Excel Options Window to Work Formula to Record Time of Data Entry in Excel

Thus, the iteration problem will be solved and you will get the exact time of data entry in Excel.

Record and Update Data Entry Time:

Now, in the previous formula, you would get the time of data entry. But you can not update a data entry time. Say, you want to update the B5 cell again. So, you should get a new time on cell C5 for the updated B5 cell. To achieve this, you have to use another formula. Go through the steps below to achieve this.

๐Ÿ“Œ Steps:

  • At the very beginning, click on cell B5 and insert the following formula.
=IF(B5<>"",IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),"")
  • Subsequently, hit the Enter key.

Formula to Record and Update Time of Data Entry in Excel

  • Afterward, for all data ranges, place your cursor in the bottom right position of the cell and drag the Fill Handle below upon its appearance.

use Fill Handle to Copy Same Formula for Recording Data Entry Time in Excel

  • Now, similarly to the previous case, insert an employee name in cell B5. and you will get entry time in cell C5.

Recorded Data Entry Time in Excel

  • And, for all the other cells, record the employee names in cell B6:B9 and you would get data entry time in cells C5 to C9.

Recorded All Data Entry Time in Excel

  • Now, click on cell B5 and record an employee name again.
  • You will see the previous entry time is changed into a new entry time as cell B5 updates.

Updated Data Entry Time in Excel

Thus, you would be able to record and update the time of data entry in Excel.

Read More: How to Automatically Insert Timestamp Data Entries in Excel


2. Creating a User-Defined Function to Record and Update Data Entry Time

Besides, you can apply a VBA code to record and update data entry time in Excel. Follow the steps below to do this.

๐Ÿ“Œ Steps:

  • First, go to the Developer tab >> Visual Basic tool.

Access Visual Basic Tool

  • As a result, the VB Editor window will appear.
  • Now, go to the Insert tab here >> Module tool.

Insert Module to Record Time of Data Entry in Excel

  • As a result, a new module named Module1 will appear.
  • Following, click on Module1 and insert the following code in the code window.
Function Entry_Time(Reference As Range)
If Reference.Value <> "" Then
Entry_Time = Format(Now, "dd-mm-yy hh:mm:ss")
Else
Entry_Time = ""
End If
End Function
  • Subsequently, press Ctrl + S.

Write VBA Code to Record Time of Data Entry in Excel

  • As a result, a Microsoft Excel window will appear.
  • Following, click on the No button.

Microsoft Excel Window

  • At this time, the Save As window will appear.
  • Now, choose the Save as type: option as .xlsm file and click on the Save button.

Save As Window

  • As a result, you have created a new formula Entry_Time in your workbook.
  • Now, click on cell C5 and insert the following formula.
=Entry_Time(B5)
  • Subsequently, hit the Enter key.

Created Formula to Record Time of Data Entry in Excel

  • For all the other cells below, use your Fill Handle feature below.

Fill Handle Feature to Copy Same Formula

  • Now, record any employee name in cell B5 and you will automatically see the time entry of the record in cell C5.

Recorded Time of Data Entry in Excel

  • Similarly, you can record other employee names in the cells below and you will get the entry time.

Finally, you would get the instantaneous time entries for all employees and the final result should look like this.

All Data Entry Time Recorded in Excel


3 More Ways to Get Instant Date and Time

Now, you might want to get instant date and time in desired cells without depending on other cellsโ€™ entries. Go through any of the ways below to accomplish this.

1. Using Keyboard Shortcut

The easiest way to get instant dates and times in Excel is to use the keyboard shortcuts.

๐Ÿ“Œ Steps:

  • First and foremost, click on cell C5.
  • Following, hold the Ctrl key and press the Semicolon (;) key on your keyboard.

Use Keyboard Shortcut to Get Instant Date

  • Thus, you will get your instant date in the cell.
  • Now, to get instant time click on cell D5 and hold Ctrl + Shift key on your keyboard.
  • Subsequently, press the Semicolon (;) key on your keyboard.

Use Keyboard Shortcut to Get Instant Time

  • Thus, you will get the instant time in your selected cell.
  • Now, to get both instantaneous date and time, click on cell E5.
  • Following, hold the Ctrl key and press the Semicolon (;) key.
  • Subsequently, press the Space bar and hold Ctrl + Shift key, and press the Semicolon (;) key.

Use Keyboard Shortcut to Get Instant Date and Time

  • Thus, you will get all time and date values for the first cell.

Instant Dates and Times

Afterward, repeat the procedures for every cell below and you will get all date and time results for all cells. And, the output should look like this.

All Instantaneous Dates and Times


2. Using TODAY Function to Show the Current Date

Besides, you can use the TODAY function to show the current date of entry. Follow the steps below to do this.

๐Ÿ“Œ Steps:

  • First and foremost, click on cell C5 and insert the following formula.
=TODAY()
  • Subsequently, press the Enter key.

Use TODAY Function to Get Instant Date

Thus, you will get the current date at cell C5. For all the other cells below use the same formula to get the current date. And, the result should look like this finally.

All Current Dates


3. Using the NOW Function to Get Instant Time

Now, you might need to get instant time in some cells. You can use the NOW function to achieve this target. Go through the steps below to do this.

๐Ÿ“Œ Steps:

  • At the very beginning, you need to format the output cells properly. To do this, select the output cells (C5:C9 here) and press Ctrl +1.

Open Format Cells Window

  • As a result, the Format Cells window will appear.
  • Now, go to the Number tab here >> choose Date from the Category: pane and choose the 3/14/12 1:30 PM option from the Type: options.
  • Last but not least, click on the OK button.

Format Cells Window

  • Now, click on cell C5 and insert the following formula.
=NOW()
  • Subsequently, hit the Enter key.

Formula to Get Instant Date and Time

  • Thus, you will get the instant time in cell C5.

You can use the same formula for all the other cells below and you will get all the instantaneous times in the cells. And, the final result should look like this.

All Instant Date and Time


Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

So, in this article, I have shown you 2 effective ways to record the time of data entry in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo