In Excel VBA, use the built-in Date function to get today’s date. To put it in a worksheet cell, assign Date to the cell’s Value; Excel stores a date value, which you can format to display as day/month/year or another pattern.
Get today’s date with VBA
Date returns the current date from the computer’s system clock, without a time component. In a VBA procedure, you can assign it to a variable declared as Date, compare it with another date, or write it straight to a cell. It is a VBA function, not a worksheet formula, so you do not put an equals sign before it in VBA code.
For example, if a variable named todayValue has been declared as Date, the assignment todayValue = Date stores the current date in that variable. The value is determined when the statement runs. If the macro runs again tomorrow, it will return tomorrow’s date, assuming the computer clock is correct.
A VBA date value is not just a piece of text that looks like a date. Excel represents dates as serial values and applies a number format to show them in a readable way. Assigning a genuine date value is usually more useful than writing a text string such as “3 November 2026”: Excel can sort, compare, calculate with, and reformat a real date.
Date versus Now: which should you use?
Use Date when you need the calendar date only. Use Now when you also need the current time. For instance, a daily report’s date stamp generally calls for Date, while a record of the exact moment a user submitted a form may need Now. Both functions read the computer’s system clock, so an incorrect clock or time zone can affect the value they return.
The distinction matters because a date-time value includes both a date and a time. If you assign Now to a cell formatted to show only a date, the time can still be stored even though it is not visible. That hidden time may affect comparisons: a cell containing today at 3 p.m. is later than a date value representing today at midnight. If you only intend to compare calendar days, use Date or explicitly remove the time component.
In a worksheet, the corresponding current-date function is TODAY(), while NOW() returns the current date and time. Those are worksheet functions; VBA’s Date and Now are used in macro code.
Insert today’s date into a cell
This macro writes the current date to cell A1 on the active worksheet and applies a day/month/year display format. It assumes the intended worksheet is active when you run it. Change the cell reference if you want another destination, and save a copy of the workbook before running the macro: macro actions cannot be undone with Ctrl+Z.
Sub WriteTodayToA1()
' Save a copy before running; macros cannot be undone with Ctrl+Z.
Dim target As Range
Set target = ActiveSheet.Range("A1")
target.Value = Date
target.NumberFormat = "dd/mm/yyyy"
End Sub
In this example, target.Value = Date writes a real date value, and target.NumberFormat controls how that value appears. The macro replaces whatever is already in A1, so choose a cell that is safe to overwrite. If the workbook has multiple sheets, avoid relying on the active sheet when the macro must always update one particular sheet. For example, you can set the range using ThisWorkbook.Worksheets("Summary").Range("A1"), provided the workbook contains a sheet named Summary.
To run a procedure in desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the procedure, and run it from the editor. On Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well, and the shortcut can depend on system settings. Excel for the web does not create or run VBA macros, so use desktop Excel for this code.
Format the date without turning it into text
The date’s stored value and its visible appearance are separate. A cell can contain today’s date and display it as 03/11/2026, 3-Nov-2026, or November 3, 2026, depending on its number format and regional settings. To keep the cell usable in date calculations, apply a cell number format rather than converting the value to a string.
Common VBA format codes include:
dd/mm/yyyyfor a two-digit day, two-digit month, and four-digit year.m/d/yyfor a shorter month/day/year display.ddd, mmm d, yyyyfor a display such as Tue, Nov 3, 2026.yyyy-mm-ddfor a year-month-day display often convenient in logs and filenames.
Those are display patterns, not instructions to change the underlying date. Be careful with numeric dates such as 04/05/2026: depending on regional conventions, a reader may interpret that as April 5 or May 4. A format with a month name, or an unambiguous year-month-day pattern, can reduce confusion when people share workbooks across regions.
The VBA Format function can also create a formatted string, but that result is text rather than a date value. Use it when you specifically need text, such as a date in a message or filename. For a worksheet cell that needs to sort and calculate as a date, assign Date and set the cell’s NumberFormat.
Choose between a fixed date and a live date
A date written by a macro is normally a snapshot: it records the date at the time the macro ran and remains unchanged tomorrow. That is the right behavior for an entry date, invoice issue date, or daily log, where the historical date should not move forward automatically. The macro must be run again to write a new date.
A worksheet formula is better when the cell should always show the current date. With the formula entered in A1 and no other cell references, use =TODAY(). Excel recalculates it according to the workbook’s calculation settings, so it can update when the workbook recalculates or is opened. It is not a permanent timestamp: if the date changes, the displayed result changes too. For a current date and time, the equivalent worksheet formula is =NOW().
For a macro-driven workflow that should refresh a report each time it is generated, writing Date is often simpler than leaving a volatile formula in the cell. For a dashboard label that should stay current without rerunning a macro, a worksheet formula may be more appropriate. Decide whether the value is meant to document an event or merely display “today”; those are different needs.
Useful date operations in VBA
Once VBA has a real date value, you can use it in ordinary date logic. For example, subtracting 1 from Date gives the previous calendar date, while adding 1 gives the next calendar date. These operations are useful for setting report periods or comparing a due date with today. For more deliberate date arithmetic—especially when moving by months or years—use VBA’s DateAdd function rather than assuming every month has the same number of days.
Use date comparisons carefully. If a worksheet cell contains a time as well as a date, comparing it directly with Date may not behave like a comparison of calendar days, because Date represents midnight at the start of the day. A date-only value assigned by the macro above avoids that particular issue. If existing data may contain times, decide whether the comparison should consider the time or only the day before writing the condition.
For a text label or a filename, format the date as text deliberately. A filename-safe pattern such as year-month-day keeps the components in a predictable order. Avoid using slashes in a filename because they are path separators on common operating systems. Keep the original date value in the worksheet if later sorting or date calculations matter, and create a separate formatted string only for the text output.
Common problems and how to avoid them
- The date appears in an unexpected order. The stored date may be correct while the number format follows a different regional convention. Set an explicit format and prefer month names or year-month-day for shared reports.
- The cell shows a number instead of a date. Excel stores dates as serial values. Apply a date number format to the cell; do not assume the value is wrong just because it is currently displayed numerically.
- The date does not update tomorrow. That is expected when VBA wrote a value. Run the macro again, or use a worksheet formula such as
=TODAY()if the cell should recalculate. - The macro writes to the wrong sheet.
ActiveSheetmeans whichever sheet is selected at run time. Refer to a named worksheet in the workbook when the destination must be fixed. - The macro will not run. VBA requires desktop Excel, and macro settings may block code from an untrusted file. Only enable macros in workbooks from a source you trust.
There is also a possible date-system issue when exchanging workbooks. Excel workbooks can use different date systems, and raw serial numbers may not mean the same calendar date in each system. Assigning VBA’s actual Date value is safer than manually writing a guessed serial number. If a workbook’s dates shift after copying data between files, check the workbook date-system setting and how the values were transferred.
Macros, platforms, and safe use
VBA macros run in the desktop versions of Excel for Windows and Mac, though keyboard shortcuts and some interface details differ. Excel for the web can display a workbook containing macros but does not run VBA. If a workbook is shared with people who use the web app, make sure the workflow does not depend on them being able to execute the macro.
Macros can change workbook contents immediately, and Ctrl+Z does not reverse a macro’s actions. Save a separate copy before running code that writes to cells, especially when adapting it to a larger range or an important workbook. Check the target worksheet and cell references first, and do not enable macros in files unless you trust their source. These precautions help prevent accidental overwrites as well as unwanted code from untrusted files.
Frequently Asked Questions
What is the VBA code for today’s date in Excel?
Use Date. For example, assigning Date to a variable declared as Date returns the computer’s current calendar date.
How do I insert today’s date into a cell with VBA?
Assign Date to a range’s Value, such as Range("A1").Value = Date. Set NumberFormat separately if you want a particular display.
Does VBA Date update automatically?
No. A macro that writes Date stores the value returned when it runs. Run it again to write a new date; use the worksheet formula =TODAY() for a date that recalculates.
What is the difference between Date and Now in VBA?Date returns the current date only. Now returns the current date and time, so its value can include a time even if the cell format hides it.
Can Excel for the web run a VBA date macro?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA.
Related
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Select a Range Using VariablesIn Excel VBA, use variables inside Cells(row, column) and pass the two cell references to Range to build…
- VBA Range Is Nothing? Meaning, Checks, and Common FixesIn Excel VBA, a Range is Nothing when an object variable does not currently refer to a range…
- 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 Date String Formatting ExplainedIn Excel VBA, use Format$ to turn a date value into a formatted text string, such as Format$(Date,…
- VBA Excel Reverse String: Code, Examples, and TipsTo reverse text in Excel VBA, use the built-in StrReverse function: StrReverse("Excel") returns "lecxE". For a reusable worksheet…
- Excel VBA Variant: What It Is and When to Use ItIn Excel VBA, a Variant is a flexible data type that can hold different kinds of values, including…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.