To add an automatic date stamp to an Excel checkbox, place the checkbox in column B, use the adjacent cell in column C for its date, and add a short Worksheet_Change VBA event to the worksheet. When a checkbox is checked, the code writes the current date as a fixed value; when it is unchecked, it clears the date. You’ll need desktop Excel for Windows or Mac and a macro-enabled workbook.
What You’ll Need
- Excel for Microsoft 365 desktop. The instructions also work in many recent desktop Excel versions, but the newer in-cell checkbox may not be available in every build.
- A worksheet with a checkbox column and a date-stamp column. This guide uses B2:B100 for checkboxes and C2:C100 for dates; you can adjust the ranges.
- Permission to use VBA macros. Excel for the web cannot run this worksheet event code.
- A saved copy of your workbook before adding or testing the code. VBA changes cannot be undone with Ctrl+Z.
Step-by-Step
- Set up the columns. Enter a heading such as Done in B1 and Date completed in C1. Leave B2:B100 for checkboxes and C2:C100 for the corresponding dates. Each checkbox and date must share a row: the checkbox in B7, for example, will write its stamp in C7.
- Insert checkboxes. If your Excel build has in-cell checkboxes, select B2:B100 and choose Insert > Checkbox. Each selected cell becomes a checkbox cell whose value is TRUE when checked and FALSE when unchecked. If that command is not available, use a Form Control checkbox instead: turn on the Developer tab through File > Options > Customize Ribbon, select Developer > Insert, and choose Check Box under Form Controls. Add one control per row. Right-click each control, choose Format Control, open the Control tab, and set its Cell link to the matching B cell, such as B2. A linked cell will show TRUE or FALSE as you check and uncheck its control.
- Open the code window for the worksheet. On Windows, press Alt+F11, or use Developer > Visual Basic. In the Visual Basic Editor, find your workbook in Project Explorer, expand Microsoft Excel Objects, and double-click the sheet that contains the checkboxes. On Mac, open the editor from Developer > Visual Basic; the editor layout may differ, but you still need the worksheet object, not a standard module or ThisWorkbook.
- Paste this event code into the worksheet window. The example watches B2:B100 and writes dates in the next column. It adds a date only if the stamp cell is blank, so checking a box again will not replace an existing date unless you first clear it. Unchecking clears the date.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim monitored As Range
Dim cell As Range
Dim stampCell As Range
Set monitored = Intersect(Target, Me.Range("B2:B100"))
If monitored Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
For Each cell In monitored.Cells
Set stampCell = cell.Offset(0, 1)
If cell.Value = True Then
If Len(stampCell.Value) = 0 Then stampCell.Value = Date
ElseIf cell.Value = False Or Len(cell.Value) = 0 Then
stampCell.ClearContents
End If
Next cell
CleanUp:
Application.EnableEvents = True
End Sub
- Adjust the watched range if needed. If your checkboxes start in D5 and continue through D200, change
Me.Range("B2:B100")toMe.Range("D5:D200"). The stamp is written one column to the right of each watched cell, so that example writes to E5:E200. Put the date column immediately next to the checkbox column, or changecell.Offset(0, 1)to point to the right destination. - Save the file as macro-enabled. In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). A standard .xlsx file cannot retain this VBA code. If Excel displays a security warning when you reopen the workbook, enable macros only if you trust the file and its source.
- Format the date cells. Select C2:C100, then choose Home > Number Format and pick Short Date or another date format. The code stores a real Excel date, so formatting changes how it appears without changing the stored value.
- Test both checkbox states. Check the box in B2 and confirm that C2 displays today’s date. Uncheck it and confirm C2 clears. Check it again and the date should be recorded again. Try another row to confirm each checkbox stamps its own matching row. If using Form Controls, click the checkbox itself; the linked cell should change before the event can respond.
Common Mistakes
- Putting code in the wrong place: Worksheet_Change must be in the code window for the sheet that contains the checkbox cells. Code in a regular module or in ThisWorkbook will not respond to that sheet’s cell changes.
- Using an unlinked Form Control: A floating checkbox control does not automatically change a worksheet cell. Set its Cell link to the correct row, or use an in-cell checkbox if available.
- Saving as .xlsx: Excel removes VBA when you save in a macro-free format. Keep the workbook as .xlsm.
- Expecting a date in the checkbox cell: The checkbox cell contains its checked state. The example deliberately writes the date to the next column so the TRUE/FALSE value remains available to Excel.
- Assuming the stamp updates continuously:
Dateis captured when the checkbox changes. It is a static date, not a formula that changes each day. - Changing the checkbox range but not the code: If your boxes are outside B2:B100, the event ignores them until you update the watched range.
Tips
Keep the checkbox and stamp columns clearly labeled, and consider protecting the date column after testing if users should not edit stamps manually. Test protection with the macro: Excel must be able to write to the stamp cells. If the sheet is protected, you may need to configure protection to allow the macro to edit those cells, or leave the stamp cells unlocked.
The example clears the date when someone unchecks a box. That suits a current-completion checklist, where the date should reflect the latest checked state. If you want to preserve the original completion date even after someone unchecks a box, remove the ElseIf block that clears the stamp. With that change, checking again will leave the first recorded date in place. Decide which behavior you want before distributing the workbook, and test it on a copy.
To capture a date and time instead of a date only, replace Date in the code with Now, then format the stamp cells with a date-and-time format. A date-only value is easier to scan in a checklist; a time stamp can help when several items may be completed on the same day.
If you do not want to use macros, you can enter a fixed date manually after checking a box. Select the stamp cell and press Ctrl+; on Windows to insert today’s date as a value. On Mac, the equivalent shortcut is Control+;. This is manual rather than automatic. Avoid using =TODAY() as a permanent stamp: it recalculates and will show a later date as time passes.
Troubleshooting
- Nothing happens when I check a box. Confirm the code is in the correct worksheet object, the box changes a cell within the watched range, and macros are enabled. With a Form Control, check its linked cell. With an in-cell checkbox, confirm the checkbox cell is inside the range in the code.
- The date appears in the wrong row or column. The sample writes to the cell one column to the right of the changed checkbox. Check that your checkbox range and stamp-column layout match that assumption. If not, change the range or the offset.
- The date is displayed as a number. The value may still be a valid date; select the stamp cells and apply a date format using Home > Number Format.
- The code worked once, then stopped. If another macro stopped events with
Application.EnableEvents = Falseand did not turn them back on, worksheet events may be disabled. In the Visual Basic Editor’s Immediate window, enterApplication.EnableEvents = Trueand press Enter. Close the editor and test again. - Excel warns that macros are disabled. Reopen the trusted .xlsm file and follow the security prompt or your organization’s approved macro policy. Excel for the web does not execute VBA; open the file in desktop Excel.
- A date is overwritten or cleared unexpectedly. Review whether the box was unchecked and whether the code’s clearing section is present. The supplied version intentionally clears the stamp on uncheck and only fills a blank stamp when checked.
Frequently Asked Questions
Can I add a date stamp without VBA?
Not automatically with a permanent date using this checkbox setup. You can type the date or use Ctrl+; on Windows (Control+; on Mac) after checking the box. A formula such as TODAY() changes over time, so it is not a reliable fixed completion stamp.
Does this work with Excel’s new in-cell checkboxes?
Yes, in desktop Excel builds that offer Insert > Checkbox. Those cells store TRUE or FALSE, which the worksheet event can detect. The Form Control method also works when each control is linked to a cell in the watched range.
Can I show the time as well as the date?
Yes. Replace Date with Now in the code and apply a date-and-time number format to the stamp cells. The time is captured when the checkbox changes.
Will the date stay fixed after I close the workbook?
Yes. VBA writes a date value into the stamp cell; it is not a volatile formula. The workbook must be saved after the change, and macros must be enabled for future checkbox changes to create new stamps.
Can I use this in Excel for the web?
You can view and edit workbook data in Excel for the web, but it does not run this VBA worksheet event. Use desktop Excel to create and update automatic stamps, or use a manual date-entry process when working only in the browser.
Related
- Adding a Checkbox to Excel: Setup, Linking, and Tips
- Average Growth Rate in Excel: Calculate It Step by Step
- How to Calculate an Average in an Excel Column
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Data Cleaning Steps in Excel: A Practical GuideTo clean data in Excel, first make a copy of the original, then inspect the columns, standardize formats…
- Excel Scatter Plot by Third Column: Color Points by GroupYes—color an Excel scatter plot by a third column by making a separate chart series for each category…
- Add Page Numbers in Microsoft Word: A Practical GuideTo add page numbers in Microsoft Word for Microsoft 365 on Windows, select Insert > Page Number, choose…
- XLOOKUP in Google Sheets: How It Works and When to Use ItYes. Google Sheets supports XLOOKUP, so you can search one range for a value and return the corresponding…
- Group Rows in Smartsheet: Sheets, Reports, and ViewsYes—Smartsheet lets you organize rows, but the right method depends on what you mean by grouping. In a…
- How to Block a Sender in Microsoft Outlook on Any DeviceTo block someone in Outlook, select one of their messages, open the message options, and choose Block or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.