Tanjim Reza
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.
Download Practice Workbook
You can download our practice workbook from here for free!
2 Effective Ways to Record and Update Time of Data Entry in Excel
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.
- Afterward, for all the other cells drag your fill handle below.
- 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.
- 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.
- Now, the formula may sometimes show errors due to iteration problems.
- To avoid this, go to the File tab.
- Afterward, click on the Options option from the expanded File tab.
- 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.
Thus, the iteration problem would 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.
- 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.
- Now, similarly to the previous case, insert an employee name in cell B5. and you would get entry time in cell C5.
- 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.
- Now, click on cell B5 and record an employee name again.
- You would see the previous entry time is changed into a new entry time as cell B5 updates.
Thus, you would be able to record and update the time of data entry in Excel.
Read More: How to Create Data Entry Form in Excel (Step by Step)
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.
- As a result, the VB Editor window will appear.
- Now, go to the Insert tab here >> Module tool.
- 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.
- As a result, a Microsoft Excel window will appear.
- Following, click on the No button.
- 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.
- 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.
- For all the other cells below, use your fill handle feature below.
- Now, record any employee name in cell B5 and you would automatically see the time entry of the record in cell C5.
- 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.
Read More: How to Design Employee Details Form in Excel (Free Template)
Similar Readings
- How to Create Data Entry Form in Excel VBA (with Easy Steps)
- Create an Autofill Form in Excel (Step by Step Guide)
- How to Create an Excel Data Entry Form without a UserForm
- Automate Data Entry in Excel (2 Effective Ways)
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.
- 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.
- 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.
- Thus, you will get all time and date values for the first cell.
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.
Read More: How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)
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.
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.
Read More: How to Design Form in Excel (2 Suitable Examples)
Similar Readings
- How to Create a Fillable PDF from Excel (2 Suitable Ways)
- Create a Printable Form in Excel (2 Easy Ways)
- How to Check for Data Entry Errors in Excel (2 Easy Ways)
- Restrict Data Entry in Excel Cell (2 Simple Methods)
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.
- 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.
- Now, click on cell C5 and insert the following formula.
=NOW()- Subsequently, hit the Enter key.
- 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.
Read More: Types of Data Entry in Excel (A Quick Overview)
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. And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!
Related Articles
- How to Create Excel Sheet from Google Form (2 Useful Methods)
- Populate an Excel Spreadsheet from a Web Form
- How to Create a Data Log in Excel (2 Suitable Ways)
- Create Data Entry Form with Drop Down List in Excel (2 Methods)
- How to Make a Fillable Form in Excel (5 Suitable Examples)
- Create an Excel Data Entry Form That Includes Checkboxes
- How to Create an Order Form in Excel (with Useful Steps)
