Excel VBA Date and Time Functions: A Practical Guide

VBA & macros · Updated November 21, 2026

Excel VBA’s main current-date and time functions are Date, Time, and Now; use DateSerial and TimeSerial to build date and time values, and DateAdd, DateDiff, and Format to work with them. These are VBA functions for macros and procedures, not Excel worksheet formulas, so the right choice depends on whether you want a value for code or a result displayed in a cell.

How VBA date and time values work

VBA stores a date and time in the Date data type. Conceptually, the value represents a day plus a fractional part for the time: noon is halfway through a day. This lets VBA compare dates, calculate intervals, and pass date values to Excel cells. A cell may display only a date, only a time, or both, even when its underlying value contains more information.

VBA’s date functions use the computer’s system clock for the current date and time. They do not create an independently updating clock inside a macro. A procedure reads the clock when it runs; a value it writes to a cell remains fixed unless another procedure changes it.

Excel also has worksheet functions such as TODAY() and NOW(). They are entered in cells and update when the workbook recalculates. VBA’s Date and Now are used in VBA code instead. The similar names can be confusing, but they operate in different contexts.

The core current-date and time functions

  • Date returns the current date, with the time set to midnight.
  • Time returns the current time, with the date set to the base date used by VBA.
  • Now returns the current date and time together.

For example, assign Now to a variable declared As Date when a macro needs a timestamp. Assign Date when it needs today’s date but should not retain the current time. Use Time for a time-of-day value alone. If you later compare a timestamp with a date at midnight, remember that the time portion can affect the comparison.

Here is a procedure that writes a timestamp to cell A1. Change the sheet name or destination to match the workbook. Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z.

Sub WriteCurrentTimestamp()
    Dim stamp As Date

    ' Read the computer's current date and time.
    stamp = Now

    With ThisWorkbook.Worksheets("Sheet1").Range("A1")
        .Value = stamp
        .NumberFormat = "yyyy-mm-dd hh:mm:ss"
    End With
End Sub

The cell receives a date/time value; its number format controls how that value appears. The format shown is a display choice, not a conversion of the value into text.

Build dates and times reliably

DateSerial(year, month, day) constructs a date from numeric components, while TimeSerial(hour, minute, second) constructs a time. These are usually safer than assembling a date from text because text interpretation can depend on regional settings. For instance, 03/04/2026 can be read as March 4 or April 3 in different locales; separate year, month, and day arguments avoid that ambiguity.

VBA normalizes component values that fall outside their usual ranges. For example, a day value beyond the end of a month rolls into the following month. That behavior can be useful for date arithmetic, but it can also hide an input error. Validate user-entered month and day values when an invalid date should be rejected rather than normalized.

Other useful functions include DateValue, which converts a recognizable date expression to a date, and TimeValue, which converts a recognizable time expression to a time. Since both parse text, the accepted interpretation can depend on the system’s regional date and time conventions. For a fixed date in code, prefer DateSerial over relying on a text date literal.

Sub BuildAppointment()
    Dim appointment As Date

    ' Build 9:30 AM on June 15, 2026.
    appointment = DateSerial(2026, 6, 15) + TimeSerial(9, 30, 0)

    MsgBox Format(appointment, "yyyy-mm-dd hh:mm")
End Sub

This example combines a date and a time into one Date value. The Format call makes a readable string for the message box; the variable itself remains a date/time value. Save a copy before running macros that write to a workbook, because Ctrl+Z cannot undo VBA actions.

Add, compare, and measure intervals

DateAdd moves a date or time by a specified number of intervals. Its common interval codes include yyyy for years, q for quarters, m for months, d for days, ww for weeks, h for hours, n for minutes, and s for seconds. The function returns a date value, so it can be assigned to a Date variable or written to a cell.

Month arithmetic has a boundary case worth knowing: adding one month to a date near the end of a month may produce the last valid day of the destination month. For example, adding one month to January 31 results in a date in late February, not a nonexistent February 31. Decide whether that calendar behavior matches the rule your process needs.

DateDiff counts interval boundaries between two dates, rather than always measuring a complete elapsed duration. A month difference can therefore be 1 even when fewer than 30 days have passed, if the dates fall on opposite sides of a month boundary. For elapsed time, subtract the two date values and interpret the result in days or fractions of a day; for calendar periods, use an interval appropriate to the question.

DatePart extracts a specified component, such as a year, month, day, hour, or week number. Year, Month, Day, Hour, Minute, and Second provide direct access to common parts. Choose an extraction function when you need a component, and DateDiff when you need to count interval boundaries between two values.

Weekdays, month names, and week numbering

Weekday(date) returns a number from 1 to 7. By default, 1 means Sunday and 7 means Saturday; you can supply a first-day-of-week argument to change the numbering. WeekdayName turns a weekday number into a name, and MonthName returns a month name from its number. These are useful for labels, but weekday numbers should not be treated as universal without specifying the intended week convention.

Week numbering needs particular care. VBA’s DatePart accepts an optional first day of week and first week of year. The defaults do not necessarily match an organization’s reporting calendar or ISO week numbering. If reports must follow a defined standard, set the options deliberately and test dates around New Year, when week-year boundaries are most likely to differ.

Neither weekday names nor month names are reliable keys for sorting. For sorting and comparisons, retain the actual date value and use a formatted name only for display. A label such as “Monday” does not identify a unique date.

Format values for display without losing date behavior

Format(dateValue, formatString) returns a string arranged according to a format pattern. For example, Format(Now, "yyyy-mm-dd hh:mm") produces a year-first date and a 24-hour time. Common pattern letters include yyyy for a four-digit year, mm for a month or minute depending on context, dd for day, hh for hour, and ss for seconds. In a format containing a time, nn is the safer VBA notation for minutes, as mm can represent month.

Use Format when the output should be text, such as a message, filename, or report label. If the result needs to remain sortable, filterable, or usable in date calculations in Excel, write the underlying date value to the cell and set the cell’s NumberFormat property instead. Converting dates to text can make later sorting and arithmetic less dependable.

Also distinguish a format from a date conversion. A cell can show only 2026-06-15 while containing a time as well; changing its display format does not remove that time. If you need a date-only value in code, use DateValue on a date value or otherwise explicitly set the time to midnight.

VBA dates versus Excel worksheet dates

In worksheet cells, Excel stores dates as serial numbers and applies a number format to display them as dates. Workbooks may use the 1900 or 1904 date system; the 1904 system is available for compatibility with older Mac workbooks. Excel’s 1900 system also preserves a historical compatibility behavior that treats 1900 as a leap year, although it was not one. VBA dates use the OLE Automation date representation and do not represent that fictitious February 29.

For ordinary macro work, pass date values through Excel’s object model rather than manually treating a displayed serial number as a universal date. Be especially cautious when exchanging raw serials between workbooks using different date systems, importing dates from other software, or handling very early dates. Confirm the workbook’s date system and the source’s convention when those edge cases matter.

Excel for Microsoft 365 on Windows supports VBA in desktop workbooks. Excel for the web can open a macro-enabled workbook but cannot create, run, or edit VBA macros. Excel for Mac supports VBA, though the Visual Basic Editor shortcut and some operating-system integrations differ. In Windows Excel, Alt+F11 opens the Visual Basic Editor; on Mac, the equivalent is generally Option+F11, sometimes with Fn depending on the keyboard settings.

Common errors and practical checks

  • Unexpected text interpretation: Avoid ambiguous date strings and prefer numeric components with DateSerial. If parsing user input, validate it and account for regional settings.
  • A timestamp fails a date-only comparison: A value from Now includes hours, minutes, and seconds. Compare dates at midnight or explicitly discard the time when the business rule is date-only.
  • Minutes appear as months: In VBA formatting patterns, use nn for minutes when ambiguity with mm is possible.
  • A displayed cell looks right but calculations fail: Check whether the cell contains a real date value or text that merely resembles a date. Formatting text does not convert it into a date.
  • Undo does not restore macro changes: Save a separate copy before running code that changes cells, because VBA changes cannot be undone with Ctrl+Z.

When debugging, inspect both the value and its display. Temporarily show a full date/time format, check the variable’s declared type, and test boundary dates such as month-end and New Year. These checks often reveal whether the problem is a hidden time component, a text conversion, or a calendar convention.

Frequently Asked Questions

What is the VBA function for today’s date?
Use Date to get the current date with the time at midnight. Use Now if the current time is needed too.

What is the difference between Date and Now in VBA?
Date returns the current date only, while Now returns the current date and time. Both read the computer’s system clock when the code runs.

How do I add days or months to a date in VBA?
Use DateAdd with an interval code such as d for days or m for months. Check month-end results if your rules require a particular treatment of dates such as January 31.

How do I get the number of days between two dates?
Subtract one date value from the other to get an elapsed number of days, which may include a fractional part if either value has a time. Use DateDiff when you specifically want to count boundaries of a chosen interval.

Can VBA date functions run in Excel for the web?
No. VBA macros run in the desktop versions of Excel, including Microsoft 365 for Windows and Excel for Mac. Excel for the web does not run VBA; use worksheet functions or another supported automation option there.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.