In Excel VBA, subtract one Date value from another to get the elapsed time in days, and use DateAdd to add a chosen interval, such as days or months. For example, DateAdd("d", 14, DateSerial(2026, 3, 10)) returns March 24, 2026. The right method depends on whether you mean calendar days, whole month steps, elapsed hours, or working days.
How VBA represents dates
VBA has a Date data type for dates and times. Internally, a date is represented as a serial value: the whole-number portion represents a day, and the fractional portion represents the time of day. For example, noon is one-half of a day. This is why subtracting two dates can produce a fractional result when the times differ.
Excel worksheet cells also store dates as numbers, then display them according to their number format. A cell containing a date may therefore show a calendar date while its underlying value is numeric. VBA’s Date type is useful because it signals that a variable is intended to hold a date or time, but it does not make ambiguous text safe: converting text such as 03/04/2026 can still depend on regional date settings.
Use DateSerial(year, month, day) to construct a date from numeric parts. It avoids relying on how a computer interprets a text date. Use Date for the current system date and Now for the current system date and time. Neither value includes a time zone.
Adding days, months, and years with DateAdd
DateAdd takes an interval name, a number of intervals, and a starting date. It returns a date value, so assign the result to a Date variable or a worksheet cell if you need to keep working with it as a date. Common intervals include "d" for days, "m" for months, "yyyy" for years, "h" for hours, and "n" for minutes.
- To add 30 calendar days, use
DateAdd("d", 30, startDate). - To move forward three months, use
DateAdd("m", 3, startDate). - To subtract one year, use
DateAdd("yyyy", -1, startDate). - To move back 90 minutes, use
DateAdd("n", -90, startDate).
The interval name is a string, and the number can be negative. A common misconception is that adding a month means adding 30 days. It does not: months have different lengths, so a month interval is a calendar operation, not a fixed number of days.
Subtracting dates and measuring elapsed time
Subtracting one VBA date from another returns a number of days, including any fraction caused by time-of-day values. If a task starts at 9:00 a.m. and ends at 3:00 p.m. on the same date, the difference is 0.25 days, or six hours. Multiply the result by 24 to express elapsed time in hours, or by 1,440 to express it in minutes.
For counting date or time interval boundaries, use DateDiff. For example, DateDiff("d", startDate, endDate) gives the difference in day intervals. The distinction matters: direct subtraction preserves fractional days, while DateDiff returns an integer count of the selected interval boundaries. A start and end time that are only minutes apart can cross an hour boundary, so DateDiff("h", ...) is not always the same as total elapsed hours.
For example, from 11:30 p.m. to 12:30 a.m. the next day, subtraction gives 1/24 of a day, or one elapsed hour. DateDiff("d", ...) returns 1 because midnight crossed a calendar-day boundary. Pick the calculation that matches the question you are answering.
Working example: add days and compare dates
Before running any macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z. This example uses DateSerial for clear, unambiguous inputs and displays both a future date and a day difference.
Sub ShowDateDifference()
Dim startDate As Date
Dim dueDate As Date
Dim daysUntilDue As Long
' Build dates from numeric parts, not regional date text.
startDate = DateSerial(2026, 3, 10)
dueDate = DateAdd("d", 14, startDate)
daysUntilDue = DateDiff("d", startDate, dueDate)
MsgBox "Due date: " & Format$(dueDate, "yyyy-mm-dd") & _
vbCrLf & "Calendar days: " & daysUntilDue
End Sub
The result is a due date of 2026-03-24 and a day count of 14. If you need the result in a worksheet instead of a message box, assign the date value to a cell and apply a date number format. Formatting changes how a value appears; it does not change the date calculation itself.
Month ends and dates that do not exist
Month arithmetic can surprise people at the end of a month. Adding one month to January 31 cannot produce February 31, because that date does not exist. VBA’s calendar adjustment returns the last valid day of the target month; depending on the year, that is February 28 or 29. The returned date may therefore not have the same day number as the starting date.
If the rule is “the last day of the following month,” calculate that explicitly with DateSerial. Passing day zero returns the final day of the previous month, so day zero of the month after next gives the end of next month:
Sub ShowNextMonthEnd()
Dim startDate As Date
Dim nextMonthEnd As Date
' Save a copy of the workbook before running a macro.
startDate = DateSerial(2026, 1, 31)
nextMonthEnd = DateSerial(Year(startDate), Month(startDate) + 2, 0)
MsgBox Format$(nextMonthEnd, "yyyy-mm-dd")
End Sub
For this example, the result is 2026-02-28. In a leap year, the equivalent calculation would return February 29. This explicit approach is often clearer for billing cutoffs, reporting periods, and month-end deadlines than assuming that adding one month preserves a day number.
Calendar days are not business days
DateAdd("d", n, date) moves by calendar days. It does not skip Saturdays, Sundays, or holidays. The "w" interval in DateAdd is also not a reliable way to calculate a business-day deadline that excludes weekends and holidays; it does not provide a holiday calendar. If a requirement says “five working days,” define what counts as a working day and use Excel’s WORKDAY worksheet function through the Excel object model, supplying a holiday range when appropriate.
Working-day rules are policy choices as well as date calculations. A business may observe different holidays by region, and some schedules use weekends other than Saturday and Sunday. Confirm the rule before automating it. Also decide whether the starting date counts as day one: adding five working days to a date usually means moving forward five eligible days, not counting the starting date as the first one.
Put date results into worksheet cells
To store a calculated date, assign it to a range’s Value property. Then set the cell’s number format if you want a consistent display. For example, the following macro writes a date 14 days after the date in A2 into B2. It assumes A2 contains a valid Excel date; it checks for a date before making the assignment.
Sub WriteDueDate()
Dim startValue As Variant
Dim startDate As Date
Dim dueDate As Date
' Save a copy of the workbook before running a macro.
startValue = ActiveSheet.Range("A2").Value
If Not IsDate(startValue) Then
MsgBox "Enter a valid date in A2."
Exit Sub
End If
startDate = CDate(startValue)
dueDate = DateAdd("d", 14, startDate)
ActiveSheet.Range("B2").Value = dueDate
ActiveSheet.Range("B2").NumberFormat = "yyyy-mm-dd"
End Sub
This writes a real date value rather than text formatted to look like a date. That difference matters if another formula, sort, or macro needs to use the result. IsDate can recognize several date-like text formats, but the meaning of ambiguous text still depends on regional settings. When possible, receive dates from cells already stored as dates or build them from numeric year, month, and day parts.
Common date-math problems
- Text is not a date value. A cell that looks like a date may contain text. Check the input and convert it deliberately; do not assume that a display format guarantees a valid date.
- Locale changes interpretation. Text such as 04/05/2026 can mean April 5 or May 4. Use numeric components with
DateSerialwhen constructing a known date. - Times are hidden by formatting. A cell formatted to show only a date can still contain a time fraction. Subtraction may therefore return a decimal even though the displayed dates look identical.
- Month steps are not fixed-length durations. Adding one month near the end of a month can land on a different day number. Specify whether the rule is a month step, a fixed number of days, or a month-end rule.
- Date systems can affect raw serials. Excel workbooks can use the 1900 or 1904 date system. Avoid copying raw date serial numbers between workbooks or systems without checking how they are interpreted; work with date values and verify imported data.
Daylight-saving changes are another source of confusion. VBA’s Date value has no time-zone metadata, so it cannot by itself distinguish a local clock time from a fixed number of real hours across a daylight-saving transition. For time-stamped events that must represent elapsed real time, store and compare consistently defined timestamps, such as UTC values, rather than relying on an unqualified local date and time.
Choosing between date methods
Use direct subtraction when you want a duration, including fractional days. Use DateAdd when you want to move a date by a named interval, such as two months or 48 hours. Use DateDiff when the requested answer is a count of interval boundaries, such as how many calendar days separate two dates. Use DateSerial to construct dates from parts or to express a deliberate calendar rule such as a month end.
There is no single best method for every deadline. For example, “14 days after receipt” is usually calendar-day arithmetic; “two months after the start date” is a calendar-month operation; and “five working days after receipt, excluding listed holidays” needs a business-day calculation. Translating the plain-language rule precisely is more important than choosing a shorter line of code.
Frequently Asked Questions
How do I add 30 days to a date in Excel VBA?
Store the starting value as a Date, then use DateAdd("d", 30, startDate). This adds 30 calendar days, including weekends and holidays.
How do I find the number of days between two dates in VBA?
Subtract the earlier Date from the later one for an elapsed duration in days, which can include a fraction. Use DateDiff("d", startDate, endDate) when you want an integer count of day boundaries.
How can I add one month without treating it as 30 days?
Use DateAdd("m", 1, startDate). It moves by a calendar month, so an end-of-month date may adjust to the last valid day in the destination month.
Does VBA date math skip weekends?
No. Ordinary addition and subtraction, and DateAdd with day intervals, include all calendar days. For a working-day deadline, use a workday calculation and provide the applicable holiday dates.
Why does subtracting two dates return a decimal?
At least one value probably contains a time component. The fractional part represents part of a day; multiply by 24 for hours. If you only want calendar dates, remove or disregard the time component deliberately rather than relying on how the cells are displayed.
Related
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Collection: Methods and Practical ExamplesVBA’s Collection object has no built-in sort method. To sort a collection, copy its items into an array,…
- Excel VBA: How to Open a WorkbookTo open a workbook with VBA, call Workbooks.Open and give it the file’s full path. For example, Set…
- Excel VBA: Find the Last Column in a Sheet or RangeTo find the last nonempty column in a specific row, start at the worksheet’s last column and move…
- Input Box in VBA: How to Use InputBoxIn VBA, an input box is a dialog that pauses a macro and asks the user to enter…
- Excel VBA String Limit: Maximum Length and Cell CapsA variable-length VBA String can hold up to approximately 2 billion characters in theory, but available memory usually…
- VBA Copy and Paste to the Last Available RowTo copy data with VBA to the first available row after existing data, find the last occupied row…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.