Automatically Add a Date and Time in Excel When Column C Changes to Yes
Question details
Create an automatic timestamp in column D when a value in column C is changed to Yes.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The worksheet needs to record the current date and time for each row when the corresponding column C cell is set to Yes.
- Observed behavior
- The desired result is for Excel to write a timestamp in column D for Yes entries and clear that timestamp if the column C value is changed to another value.
Use the desktop version of Excel because Excel for the web does not run VBA macros. Decide which worksheet should use the timestamp rule, since the code must be added to that worksheet's code module rather than to a standard module.
Add a Worksheet_Change macro to timestamp Yes values in column C
Use this method when you want Excel to enter a fixed current date and time in column D whenever a cell in column C is changed to Yes.
The event macro monitors column C only. It writes the current date and time to the adjacent cell in column D when the edited value is Yes, and clears column D when the value is changed to something else.
In desktop Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor.
In the Project Explorer, expand VBAProject for your workbook, expand Microsoft Excel Objects, and double-click the worksheet where users will edit column C.
Paste this code into the worksheet code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range Dim rng As Range Set rng = Me.Columns("C") If Intersect(Target, rng) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False For Each cell In Intersect(Target, rng) If UCase$(CStr(cell.Value)) = "YES" Then cell.Offset(0, 1).Value = Now Else cell.Offset(0, 1).ClearContents End If Next cell CleanUp: Application.EnableEvents = True End Sub
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Close and reopen the file if Excel prompts you to enable macros.
Type Yes in a cell such as C2 and press Enter. Excel places the current date and time in D2; changing C2 to No or another value clears D2.
Format the timestamp column to show both date and time
Use this after adding the macro if Excel shows a number, date only, or time only in column D.
Create automatic Yes-entry timestamps with WPS Spreadsheets
WPS Spreadsheets provides a familiar worksheet environment for editing macro-enabled workbooks and creating timestamp workflows. Add the event code to the relevant sheet, save the file as XLSM, and use column C changes to populate date and time values in column D.
- 1. Open the macro-enabled workbook: In WPS Spreadsheets, open the workbook containing the worksheet where column C will be edited.
- 2. Open the VBA editor: Use the Developer tools or the VBA editor entry point in WPS Spreadsheets to access the target worksheet's code module.
- 3. Add the worksheet event code: Paste the Worksheet_Change procedure into the code module for the specific worksheet, not a general module.
- 4. Save as an XLSM file: Choose Save As and keep the Excel Macro-Enabled Workbook (*.xlsm) format so the macro code is retained.
- 5. Enter Yes in column C: Edit a cell in column C to Yes and verify that the adjacent cell in column D receives the current date and time.
Frequently Asked Questions
Why does the macro need to be placed in the worksheet module?
Worksheet_Change is a worksheet event. Place it under Microsoft Excel Objects by double-clicking the specific sheet, such as Sheet1, in the Visual Basic Editor. It will not trigger if pasted into a standard module.
Can this macro timestamp several pasted Yes values at once?
Yes. The For Each cell loop checks every changed cell in column C, so pasting Yes into multiple cells in that column adds a timestamp beside each one.
Why is no timestamp added when I edit the workbook in Excel for the web?
Excel for the web does not run VBA macros. Open the XLSM file in the desktop Excel application, enable macros if prompted, and then edit column C.
How do I keep the timestamp when column C is changed from Yes later?
Remove or change the Else section containing cell.Offset(0, 1).ClearContents. As written, the code intentionally clears the column D timestamp whenever the corresponding column C value is not Yes.
Can I use a different trigger column or timestamp column?
Yes. Change Me.Columns("C") to the column you want to monitor. The current cell.Offset(0, 1) writes one column to the right; adjust the offset if the timestamp should appear in another column.
