How to Record Time of Data Entry in Excel: 5 Effective Methods

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

Steps:

  • Click on cell C5 and insert the following formula.
=IF(B5<>"",IF(C5<>"",C5,NOW()),"")
  • Hit the Enter key.

Formula to Record Time of Data Entry in Excel

Use Fill Handle Feature to Copy Same Formula

  • Your automated formula for record of data entry time is established.
  • Insert any employee name in cell B5.
  • There will be an instant time of data entry stamped in cell C5.

Recorded Time of Data Entry in Excel Through Formula

  • Look at the created 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

  • The formula may sometimes show errors due to iteration problems.
  • Go to the File tab.

Access the File Tab

  • Click on the Options option from the expanded File tab.

Options Option

  • The Excel Options window will appear.
  • Go to the Formula tab here >> choose the Automatic option from the Workbook Calculation options >> tick on Enable iterative calculation option >> click OK button.

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

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

Record and Update Data Entry Time:

In the previous formula, you would get the time of data entry, but you can not update a data entry time. To update the B5 cell again, you should get a new time on cell C5 for the updated B5 cell.

Steps:

  • 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())),"")
  • Hit Enter.

Formula to Record and Update Time of Data Entry in Excel

  • 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

  • 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

  • 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

  • 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

You would be able to record and update the time of data entry in Excel.


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

Steps:

  • Go to the Developer tab >> Visual Basic tool.

Access Visual Basic Tool

  • The VB Editor window will appear.
  • Go to the Insert tab here >> Module tool.

Insert Module to Record Time of Data Entry in Excel

  • A new module named Module1 will appear.
  • 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
  • Press Ctrl + S.

Write VBA Code to Record Time of Data Entry in Excel

  • A Microsoft Excel window will appear.
  • Click the No button.

Microsoft Excel Window

  • The Save As window will appear.
  • Choose the Save as type: option as .xlsm file and click on the Save button.

Save As Window

  • You have created a new formula Entry_Time in your workbook.
  • Click on cell C5 and insert the following formula.
=Entry_Time(B5)
  • Hit Enter.

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

  • 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

  • Record other employee names in the cells below to get the entry time.

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

Method 1 – Using Keyboard Shortcut

Steps:

  • Click on cell C5.
  • Hold the Ctrl key and press the Semicolon (;) key on your keyboard.

Use Keyboard Shortcut to Get Instant Date

  • Get your instant date in the cell.
  • Get instant time click on cell D5 and hold Ctrl + Shift key on your keyboard.
  • Press the Semicolon (;) key on your keyboard.

Use Keyboard Shortcut to Get Instant Time

  • Get the instant time in your selected cell.
  • Get both instantaneous date and time, click on cell E5.
  • Hold the Ctrl key and press the Semicolon (;) key.
  • Press the Space bar and hold Ctrl + Shift key, and press the Semicolon (;) key.

Use Keyboard Shortcut to Get Instant Date and Time

  • Get all time and date values for the first cell.

Instant Dates and Times

Repeat the procedures for every cell below and you will get all date and time results for all cells. The output should look like this.

All Instantaneous Dates and Times


Method 2 – Using TODAY Function to Show the Current Date

Steps:

  • Click on cell C5 and insert the following formula.
=TODAY()
  • Press the Enter key.

Use TODAY Function to Get Instant Date

Get the current date at cell C5. For all the other cells below use the same formula to get the current date. The result should look like this finally.

All Current Dates


Method 3 – Using the NOW Function to Get Instant Time

Steps:

  • You need to format the output cells properly. Select the output cells (C5:C9 here) and press Ctrl +1 to do this.

Open Format Cells Window

  • The Format Cells window will appear.
  • 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.
  • Click the OK button.

Format Cells Window

  • Click on cell C5 and insert the following formula.
=NOW()
  • Hit Enter.

Formula to Get Instant Date and Time

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


Related Articles


<< Go Back to Data Entry in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo