Skip to content

Automatically Date and Time Stamp Data Entry in Excel

     

    OK so you want to automatically date and time stamp your data entry in Microsoft Excel.  In this video I will demonstrate two methods you can use to achieve this.  The first method uses a formula and the second method uses a VBA macro.

    Click here to download the featured file

    Formula Method

    Watch the video walk-though here: https://youtu.be/23-RoK1p7tg?t=52

    This method ue an IF formula to return a date and time stamp.

    The first step is to format the cells that will eventually contain your date and time stamps with the correct format.

    1. Select the cells
    2. Use the shortcut CTRL 1 or right-click Format Cells to open the Format Cells dialog box
    3. On the Number tab, select Custom at the bottom of the Category list
    4. In the Type box, type the following: dd/mm/yyyy hh:mm
    5. Click on OK to confirm

    We can use the NOW() function to return the current date and time in Excel, but how do we stop the NOW() function changing the date and time whenever the worksheet is edited or refreshed?

    The formula I used in this video to create the date and time stamp is =IF(A2<>””,IF(B2=””,NOW(),B2),””)  This formula only records the date and time of the original entry and will not update if changes are subsequently made.

    A2 is the cell that contains the data and B2 is the cell that will contain the date and time stamp.  This formula creates a circular reference as it refers to cell B2, which is the cell the formula is in.  Because of this you need to change a setting in Excel to get the formula to work.
    To change the setting in Excel:

    1. Click on the ribbon’s File tab
    2. Select Options (bottom left of screen)
    3. Select Formulas (on the left)
    4. In the Calculation options section (first section) tick Enable iterative calculations
    5. Change the Maximum Iterations setting to 1
    6. Click OK to confirm

    VBA Macro Method

    Watch the video walk-through here: https://youtu.be/23-RoK1p7tg?t=244

    The second method uses a VBA macro to return a date and time stamp.  This method will record the date and time of the original entry but will also record the date and time of the most recent update in a separate column.

    As we are using a VBA macro for this method, your Excel workbook will need to be saved as a macro-enabled workbook.
    You will also want to show the Developer tab on the ribbon.  To do that:

    1. Right-click on any visible ribbon tab and select Customize the Ribbon
    2. Tick Developer in the list of Main Tabs
    3. Click OK to confirm

    The next step is open the Visual Basic Editor (VBE).  You can open the VBE by either clicking the Visual Basic button on the Developer tab or by using the shortcut ALT F11.

    The macro we are running needs to be triggered by the worksheet ‘change’ event.

    1. In the Project Explorer (on the left), double-click the worksheet you are working in and want the macro to apply to
    2. Above the Code window, change the first drop-down from (General) to Worksheet
    3. In the second drop-down select Change
    4. You can then delete the last two lines of code that appear in the Code window

    Between the two lines of remaining code, paste the following:

    Dim MyData As Range
Dim MyDataRng As Range
Set MyDataRng = Range("A2:A10")
If Intersect(Target, MyDataRng) Is Nothing Then Exit Sub
On Error Resume Next
If Target.Offset(0, 1) = "" Then
    Target.Offset(0, 1) = Now
End If
Target.Offset(0, 2) = Now
For Each MyData In MyDataRng
    If MyData = "" Then
        MyData.Offset(0, 1).ClearContents
        MyData.Offset(0, 2).ClearContents
    End If
Next MyData

    For a detailed walk-through of the code, please see the video above.