Automatically Add Date/Time to a Cell upon Entry

Enter a static date, or date and time, into a corresponding cell after data is entered into other cells.

You can easily automate the insertion of date/time information into a cell by using the TODAY( ) or NOW( ) function, but if the date entered must be static, you'll need this tutorial.

Let's suppose you've set up some data and you want the current date entered into column B when data is entered in column A in the same row.

Add the following code to the Private module of the worksheet that will store the data and corresponding date. To quickly get there from Excel, right-click on the sheet name tab, choose ViewCode, and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
	If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
	    With Target(1, 2)
		.Value = Date
		.EntireColumn.AutoFit
	    End With
	End If
End Sub

Exit the VBE and save your workbook.

Now, test it by adding any data to any cell in the range A2:A100. You will see the current date appear in the corresponding cell of B2:B100.

To get both the current date and time use .Value = Now as opposed to .Value = Date. For only the time, use .Value = Time. If you want to hardcode the date in only a fewinstances, you can use the Ctrl-: shortcut (hold down the Ctrl key and press the colon key).