In Excel VBA, use Now to get the current date and time from the computer’s system clock. For the date alone, use Date; for the time alone, use Time. For example, assigning Now to a VBA Date variable captures a timestamp you can write into a cell or use in a calculation.
The basic VBA Now example
This macro puts the current date and time into cell A1 on the active worksheet:
Sub PutCurrentTimestampInA1()
Dim stamp As Date
' Capture the current system date and time.
stamp = Now
ActiveSheet.Range("A1").Value = stamp
End Sub
Before running any macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z, so a backup gives you a way to recover if a macro changes the wrong cells. In Microsoft 365 for Windows, you can open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and run the macro from the editor. On a Mac, open the editor from Developer > Visual Basic; the keyboard shortcut may be Option+F11 or Fn+Option+F11, depending on the keyboard.
The value assigned by Now is a date-and-time value, not just text that looks like a timestamp. Excel can display it in different formats, compare it with other date values, and use it in calculations. The exact display depends on the cell’s number format.
What Now returns
Now is a built-in VBA function. It returns a value of the VBA Date type representing the computer’s current local date and time when the function is called. The time comes from the system clock; VBA does not independently verify that the clock or time zone is correct. If Windows or macOS has an incorrect clock, the timestamp can be incorrect too.
A date-and-time value contains both a calendar date and a time of day. In Excel, dates are represented internally as serial numbers, with the fractional part representing the time. For example, a value with a fraction of one-half represents noon. You usually do not need to work with the serial number directly: formatting the cell as a date and time makes the stored value readable.
One important distinction is that Now is evaluated when VBA reaches that line. It is not a continuously updating clock. If a macro stores the result in a variable and then runs for several minutes, that variable keeps the original timestamp unless the code calls Now again.
Now vs. Date and Time
Choose the function that matches the information you actually need. These functions are related, but their returned values are not interchangeable in every context:
Nowreturns the current date and current time together. Use it for an event timestamp, such as recording when a task was completed.Datereturns the current date with the time set to midnight. Use it for date-only reports or comparisons where hours and minutes should not matter.Timereturns the current time of day. Use it when the calendar date is irrelevant, such as recording a time-of-day reading.
For example, with stamp declared as Date, the assignment stamp = Date is appropriate for a date-only value, while stamp = Now preserves the time as well. A frequent misconception is that Date means the full current timestamp because the VBA type is named Date. It does not: the Date function returns the date, while Now supplies both components.
Write a timestamp and control its display
To put a timestamp in a different cell, change the range in the example. This version writes a timestamp to B2 and applies a readable date-and-time number format:
Sub WriteFormattedTimestamp()
Dim stamp As Date
Dim target As Range
' Save a copy of the workbook before running this macro.
stamp = Now
Set target = ActiveSheet.Range("B2")
target.Value = stamp
target.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub
The format string controls how Excel displays the value, not the stored timestamp. In Excel number formats, mm means month when used in a date portion, and minutes when used in a time portion. The surrounding date and time tokens make the intended meaning clear in the example. You can omit seconds or choose another display, such as a month/day/year format, without changing the underlying value.
Use NumberFormat when you want Excel to retain a true date-and-time value that can be sorted or calculated. If you instead convert a timestamp to a string with VBA’s Format$ function, the result is text. Text can be useful for labels or exported messages, but it may not behave like a real date in later date calculations. Keep the value as a date unless you specifically need text.
Capture a timestamp once or show a live clock
For a permanent record, a macro should write the value of Now into a cell. The timestamp then stays at the value captured during that run, even after the clock moves forward. This is useful for logging when an action happened. It does not update automatically when the workbook recalculates or when the file is reopened.
A worksheet formula has different behavior. In cell A1, enter =NOW() to display the current date and time as an Excel formula result. Excel recalculates this volatile function when calculation occurs, so it is not a reliable way to keep an unchanging audit timestamp. A formula that recalculates is useful when you want a current display; a macro that writes a value is usually the better fit when you need a fixed record.
Neither approach is a clock that refreshes every second by default. A formula updates during recalculation, and a VBA macro runs only when triggered. Building a continuously refreshing clock requires extra code and can affect workbook responsiveness, so it is usually unnecessary for a log or ordinary report.
Use Now in a worksheet change event
A common request is to record when a user edits a particular input cell. A worksheet change event can place a fixed timestamp in a neighboring cell. This example belongs in the code module for the specific worksheet, not in a standard module. In the Visual Basic Editor, open that worksheet’s code window and use:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim watchedCell As Range
' Ignore changes outside the input cell.
Set watchedCell = Me.Range("B2")
If Intersect(Target, watchedCell) Is Nothing Then Exit Sub
' Record the time of the edit in C2.
Me.Range("C2").Value = Now
Me.Range("C2").NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub
Save a copy before enabling or using event code as well; the code changes cell contents and Ctrl+Z will not reverse a macro’s action. In this example, an edit to B2 writes a timestamp to C2. It records the time the event handler runs, not a verified identity or an independently secured audit trail. Users can edit or remove the timestamp, and workbook macros may be disabled by security settings.
For multi-cell pastes, this example still writes one timestamp if the changed range includes B2. If your intended behavior is different—for example, timestamping every affected row—you need to define that rule and adapt the code. Event procedures should also be written carefully to avoid unintended loops if they modify cells that trigger further events.
Common problems and troubleshooting
- The cell shows a number: The value may be stored correctly but displayed with a General or numeric format. Set
NumberFormatto a date-and-time pattern, or use the Home tab’s number-format controls. - The time is missing: The cell may be formatted to show only a date. Apply a format containing hours and minutes, such as
yyyy-mm-dd hh:mm. - The value changes when expected not to: Check whether you used the worksheet formula
=NOW()instead of writing a VBA value. A formula recalculates; a value written by a macro remains fixed. - The timestamp is wrong by an hour or more: Check the computer’s date, time, and time-zone settings. VBA
Nowuses local system time and does not convert it to UTC. - The macro does not run: Confirm that macros are permitted for the workbook and that the code is in the right place. A worksheet event must be in that worksheet’s code module; a regular manually run macro belongs in a standard module.
If the value looks like text, check how it was assigned. A string produced by a formatting function may display like a timestamp but cannot be treated exactly like a numeric date value. If you need sorting, date subtraction, or date-based filters, write the original Date value and format the cell rather than saving only a formatted string.
Choosing between VBA and worksheet functions
Use VBA Now when an action should write a value, such as a button click or a worksheet event. Use the worksheet function =NOW() when the cell should show a recalculating current date and time. Use Date when the time component has no value for your task. These choices address different needs, so one is not universally better than the others.
For a button-driven timestamp, a small macro can write Now to the selected cell or a designated log column. For a report heading that should reflect the current time when the workbook recalculates, a formula may be simpler. If a timestamp must be tamper-resistant or legally reliable, a normal Excel cell is not sufficient by itself; VBA and worksheet protection do not create a secure audit system.
The code examples apply to Excel for Microsoft 365 on Windows and use standard VBA features also available in Excel for Mac. Excel for the web can open and edit many workbooks, but it does not run VBA macros. To execute these macros, use the desktop Excel application. Menu placement and keyboard shortcuts can differ on a Mac, but the VBA function names and core code are the same.
Frequently Asked Questions
What is the VBA code for the current date and time?
Use Now. For example, Dim stamp As Date followed by stamp = Now stores the current local date and time in a variable.
How do I get only today’s date in VBA?
Use the built-in Date function. It returns the current date with no time-of-day component. Use Now instead if the hours, minutes, or seconds matter.
Does VBA Now update automatically?
No. Now returns the system date and time when that line of code runs. A stored variable or cell value will not keep ticking forward unless code writes a new value.
What is the difference between VBA Now and Excel NOW()?
VBA Now is called by code and returns a value at that point in the macro. The worksheet formula =NOW() is recalculated by Excel, so its displayed result can change when calculation occurs.
Can I use VBA macros in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA code.
Related
- Excel Split Cell Into Multiple Cells: Methods That Work
- Excel Split Names in One Cell Into Two Cells
- Excel Split One Cell Into Multiple Rows: How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Block Comments: How to Comment Out Code SafelyExcel VBA has no special multi-line block-comment syntax such as /* … */. To comment out several lines,…
- Vba loop through rows?Learn how to loop through Excel rows with VBA, find the last row, handle conditions and deletions, and…
- VBA Dictionary For Each Key: How to Loop Through ItemsTo loop through every key in a VBA dictionary, use For Each key In dict.Keys, declaring key as…
- Round Up in Excel VBA: Methods, Rules, and ExamplesTo round numbers up in Excel VBA using Excel’s familiar “away from zero” rule, call Application.WorksheetFunction.RoundUp(number, num_digits). For…
- Excel VBA Left Function: Syntax, Examples, and TipsIn Excel VBA, Left returns a specified number of characters from the start of a string: for example,…
- Excel VBA: Bring a Workbook Window to the Front ReliablyTo bring an open workbook to the front with Excel VBA, activate Excel and then activate the workbook:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.