To add today’s date to a cell with Excel VBA, assign the VBA Date value to that cell, then set its display format if needed. For example, ActiveCell.Value = Date writes a fixed date into the currently selected cell; use Now instead if you also need the current time.
Choose between a fixed date and a live date
VBA’s Date function returns the computer’s current date without a time component. When a macro writes that value into a cell, Excel stores a date value that stays the same until something changes it. This is usually what people mean by “add a date”: the cell records when a task, entry, or event was handled, rather than continually showing the date today.
A live date behaves differently. A worksheet formula such as =TODAY() recalculates to the current date, so it is useful for a dashboard or report that should always show today. With the date intended for display in B2, enter =TODAY() in B2. It is not a permanent timestamp: tomorrow, the formula can display tomorrow’s date. A VBA macro that assigns Date creates a static value instead.
Choose the value that matches the job:
- Use
Datefor a date-only record, such as the day an order was entered. - Use
Nowfor a date and time, such as when a record was submitted. - Use
=TODAY()when the displayed date should update automatically.
Excel stores dates as numbers and applies a number format to display them as calendar dates. This means the date can be used in sorting, date calculations, and filters; it is not merely text that looks like a date.
Write today’s date to the selected cell
For a quick macro that places the date in whichever cell is active, put this procedure in a standard VBA module. The format shown is year-month-day, such as 2026-11-03. Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z.
Sub PutDateInActiveCell()
Dim targetCell As Range
' Remember the selected cell, then write today's date.
Set targetCell = ActiveCell
With targetCell
.Value = Date
.NumberFormat = "yyyy-mm-dd"
End With
End Sub
To add this code in Excel for Microsoft 365 on Windows, open the workbook, press Alt+F11 to open the Visual Basic Editor, and choose Insert > Module. Paste the procedure into the module, close or switch away from the editor, select a cell in the worksheet, and run the macro. To run it using the ribbon, choose Developer > Macros, select PutDateInActiveCell, and select Run. If the Developer tab is not visible in Windows, enable it through File > Options > Customize Ribbon, then select Developer.
On a Mac, Excel also supports VBA, though keyboard shortcuts and some interface details can differ by keyboard and version. Use the Developer tab to open the Visual Basic Editor or the Macros list if those controls are available. A built-in alternative for inserting a fixed date is Ctrl+; on Windows and Control+; on Mac; keyboard layouts can affect the keystroke. That shortcut enters a date directly and does not require a macro.
Put the date in a specific cell
If the macro should always write to a known location, refer to that worksheet cell directly rather than relying on the active selection. The following example writes today’s date to B2 on the sheet containing the code:
Sub PutDateInB2()
Dim targetCell As Range
' Set the destination on the active worksheet.
Set targetCell = ActiveSheet.Range("B2")
targetCell.Value = Date
targetCell.NumberFormat = "yyyy-mm-dd"
End Sub
For a workbook where users may select a different sheet before running the macro, a specific worksheet reference is safer. Replace Sheet1 with the worksheet’s VBA code name, or use the exact tab name in a quoted worksheet reference. For example, with a worksheet tab named Entry Form, the destination can be written as ThisWorkbook.Worksheets("Entry Form").Range("B2"). The code name approach is less sensitive to someone renaming the visible worksheet tab, while a tab-name reference is often easier to recognize.
Also decide whether each run should overwrite an existing value. The examples above overwrite B2 every time. To preserve an existing date, add a condition that checks whether the target cell is empty before assigning the value. That matters in shared data-entry workflows: an accidental second run should not silently replace the original date if the date is meant to record the first entry.
Use a timestamp when the time matters
For an exact date-and-time value, assign Now rather than Date. The cell’s number format determines whether users see the time. This procedure writes the current date and time into the active cell:
Sub PutTimestampInActiveCell()
Dim targetCell As Range
' Store the current date and time in the selected cell.
Set targetCell = ActiveCell
With targetCell
.Value = Now
.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End With
End Sub
Excel stores the date and time together as a numeric value, with the time represented as a fraction of a day. If you format a timestamp cell as date-only, the time is still stored but hidden; changing the format can reveal it. Conversely, assigning Date does not capture the current time. A timestamp recorded by this macro uses the computer’s local date and time settings, so it should not be treated as a standardized time-zone record without additional handling.
Stamp a date when someone enters a row
A worksheet event macro can record a date automatically when a user enters information. This example watches A2:A1000 and puts the date in column B of the same row, but only if B is blank. Because it is an event procedure, place it in the code module for the relevant worksheet—not in a standard module. In the VBA editor, find the worksheet under Microsoft Excel Objects and open its code window.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim watchedCells As Range
Dim changedCell As Range
' Watch entries in column A, rows 2 through 1000.
Set watchedCells = Intersect(Target, Me.Range("A2:A1000"))
If watchedCells Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
' Handle single-cell edits and pasted ranges.
For Each changedCell In watchedCells.Cells
If Len(changedCell.Value) > 0 Then
If Len(Me.Cells(changedCell.Row, "B").Value) = 0 Then
With Me.Cells(changedCell.Row, "B")
.Value = Date
.NumberFormat = "yyyy-mm-dd"
End With
End If
End If
Next changedCell
CleanUp:
Application.EnableEvents = True
End Sub
The event handler responds to edits in the watched range, including a paste covering multiple cells. It writes a date only when the corresponding B cell is empty, so editing A again does not refresh the original stamp. If the user deletes the value in A, the date in B remains; deleting the source does not automatically erase the stamp. Adapt those rules deliberately if your process needs a different meaning for “date added.” The error-cleanup section restores event handling if the procedure encounters a run-time error after disabling events.
Macros must be enabled for this automation to run, and the workbook must be saved in a macro-enabled format such as .xlsm. A worksheet-change event is not a substitute for a protected audit log: users with permission to edit the workbook can still alter the stamped value. Since VBA actions cannot be undone with Ctrl+Z, test the event on a copy before using it with important records.
Format the result and avoid common problems
The assigned value and the display format are separate. .Value = Date sets the value; .NumberFormat = "yyyy-mm-dd" tells Excel how to show it. If you leave out the format line, Excel may display the date according to the cell’s existing format or the workbook’s regional settings. If a cell shows a number such as 46329 instead of a calendar date, it may contain a valid date serial with a General or numeric format. Apply a date format rather than changing the stored value.
Common problems and checks include:
- The macro writes to the wrong cell:
ActiveCellmeans the selected cell when the macro runs. Select the intended cell first, or refer to a fixed worksheet range. - The date does not change: A value written by
Dateis fixed. Run the macro again only if a new date is wanted; use=TODAY()for a date that updates. - The event macro does nothing: Confirm the code is in the correct worksheet module, macros are enabled, and the edit is inside the watched range.
- The event seems to stop responding after an error: Check whether application events were left disabled. The cleanup code above re-enables them after an error in the procedure.
- The date appears in an unexpected order: Use an unambiguous format such as yyyy-mm-dd, or choose a date format appropriate for the people who will read the workbook.
Do not convert a date to text just to make it look a certain way unless text is specifically required. Text dates can sort differently from real dates and may not work as expected in date arithmetic, grouping, or date filters. If you need a different appearance, change the number format while keeping the underlying value numeric.
When a formula or manual entry is a better choice
VBA is useful when a date should be entered as part of a repeatable action or when a row should receive a one-time stamp. It may be unnecessary for a simple workbook. For a single date, type it into the cell or use the fixed-date keyboard shortcut. For a date that should update each day, enter =TODAY() in the destination cell. A formula is easier to inspect than a macro, but it does not preserve the date on which a particular row was first entered.
For a lightweight manual workflow, select the destination cell and use Ctrl+; on Windows or Control+; on Mac to insert today’s date as a fixed value. This is useful when only occasional entries need a stamp and the workbook should not contain executable code. For recurring data entry across many rows, an event macro reduces manual work, but it also requires a macro-enabled file and appropriate trust settings. Pick the simplest method that reliably captures the meaning of the date.
Frequently Asked Questions
How do I insert today’s date into a cell using VBA?
Use Range("A1").Value = Date to put today’s date in A1 on the active worksheet. Add a number format such as Range("A1").NumberFormat = "yyyy-mm-dd" if you want a consistent display.
What is the difference between Date and Now in VBA?Date returns the current date without a time. Now returns the current date and time. The cell’s number format controls which parts are visible.
How can I stop VBA from changing a date every day?
Write the value with VBA’s Date function rather than using a =TODAY() formula. The macro writes a fixed value that remains unchanged unless a person or another macro edits it.
Can I automatically add a date when a cell is filled?
Yes. Use a Worksheet_Change event procedure in that worksheet’s code module, check whether the changed cell is in the input range, and write Date to the corresponding date cell. Test it on a workbook copy first.
Why does Excel display a number instead of a date?
Excel may be showing the date’s underlying serial number because the cell uses General or a numeric format. Set the cell’s number format to a date format; the date value usually does not need to be rewritten.
Related
- Excel Split Empty Cells Into Multiple Rows?
- Excel Split Cell Text Into Separate Cells: A Practical Guide
- Excel Split Cell Value by Delimiter: Methods and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA InputBox New Line: Add Line Breaks to PromptsTo put a new line in a VBA InputBox prompt, join the prompt text with vbCrLf or vbNewLine.…
- Excel VBA: How to Exit a For Next Loop Early in Your MacroYes. In Excel VBA, put Exit For inside a For...Next loop to leave it as soon as a…
- Excel VBA Function Not Defined? Causes and Fixes ExplainedIf Excel VBA reports Sub or Function not defined, it cannot find the procedure named in the highlighted…
- VBA Dictionary Declaration: Setup, Methods, and ExamplesTo declare a VBA Dictionary in Excel for Microsoft 365 on Windows, use Dim dict As Object followed…
- VBA in Excel: Open the Editor with a Keyboard ShortcutTo open the VBA editor in Excel for Microsoft 365 on Windows, press Alt+F11. This opens the Visual…
- Excel VBA Cell Background Color: Fill Cells in CodeTo change a cell’s background color with Excel VBA, set its Interior.Color property—for example, Range("A1").Interior.Color = RGB(255, 255,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.