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).
In this tutorial:
- Retrieve a Workbook's Name and Path
- Excel's Three-Criteria Limit for Conditional Formatting
- Run Procedures on Protected Worksheets
- Distribute Macros
- Automatically Add Date/Time to a Cell upon Entry
- Create a List of Workbook Hyperlinks
- Find a Number Between Two Numbers
- Name a Workbook with the Text in a Cell
- Sort Worksheets
- Password-Protect a Worksheet from Viewing