To add days to a date in Excel VBA, use DateAdd("d", numberOfDays, dateValue) or add a whole number directly to a date value. For example, a macro can put a date seven days after the date in A2 into B2; the result is a real date value that you can format for display.
How VBA date addition works
Excel stores dates as serial values, and VBA represents dates with the Date data type. A whole-number increment moves the date forward by that many calendar days: adding 1 advances one day, while adding -1 moves back one day. This makes direct addition simple when your input is already a valid date and your offset is an integer.
DateAdd makes the intent explicit. Its first argument identifies the interval, the second gives the amount, and the third is the starting date. Use "d" for calendar days. The function returns a date, so it can be assigned to a worksheet cell, a Date variable, or another expression. For example, adding 14 days to 20 June 2026 returns 4 July 2026.
Use a negative amount to calculate a date in the past. If a cell contains an offset, the macro can read it rather than hard-coding a number. Make sure the offset represents a whole number of days. If a decimal is supplied to a date interval operation, VBA may round it; it is safer to define whether fractional inputs should be rejected or deliberately converted.
DateAdd or direct addition?
Both approaches are useful, and for ordinary calendar-day calculations they produce the same date. Direct addition is concise and works naturally with date serials. DateAdd("d", days, startDate) is often easier to read in a longer procedure because it labels the interval as days. It also fits a pattern you can adapt to other date intervals, such as months or years, though those intervals have different calendar behavior.
Choose based on readability and the kind of input you have. For a short calculation with a known integer, adding the number is clear. For reusable code or a value read from a cell, DateAdd makes the operation easier to recognize later. Neither method automatically skips weekends or public holidays. They count calendar days, so adding 5 to a Friday lands on Wednesday.
For either approach, the starting value must be a valid date. A string that looks like a date can be interpreted according to the computer’s regional settings, so dates such as 03/04/2026 may be ambiguous. If dates come from imported text, normalize or validate them before calculating instead of relying on how a particular computer interprets the text.
A simple macro for one date
This example reads a date from A2 on a worksheet named Sheet1, adds seven calendar days, and writes the answer to B2. It checks that A2 contains something VBA recognizes as a date before converting it. Change the sheet name, source cell, destination cell, or day count to fit your workbook.
Sub AddSevenDays()
Dim ws As Worksheet
' Set the worksheet used by this example.
Set ws = ThisWorkbook.Worksheets("Sheet1")
If IsDate(ws.Range("A2").Value) Then
ws.Range("B2").Value = DateAdd("d", 7, CDate(ws.Range("A2").Value))
' Set display format; this does not change the stored date.
ws.Range("B2").NumberFormat = "m/d/yyyy"
Else
MsgBox "Enter a valid date in A2.", vbExclamation
End If
End Sub
To run it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the procedure there. Run it with the cursor inside the procedure and press F5, or return to Excel and run it from Developer > Macros. The worksheet must be named exactly Sheet1 unless you edit the code.
Adding different day counts down a worksheet
For a list, a common layout is a starting date in column A, the number of days to add in column B, and the calculated result in column C. This macro processes rows from 2 to the last nonempty cell in column A. It clears the result when the row does not contain a recognizable date and a numeric offset. The example expects offsets to be whole numbers; use integer values in column B.
Sub AddDaysForEachRow()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Dim startValue As Variant
Dim dayValue As Variant
' Change Sheet1 if your worksheet has a different name.
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
startValue = ws.Cells(r, "A").Value
dayValue = ws.Cells(r, "B").Value
If Len(startValue) > 0 And IsDate(startValue) And IsNumeric(dayValue) Then
ws.Cells(r, "C").Value = DateAdd("d", CLng(dayValue), CDate(startValue))
Else
ws.Cells(r, "C").ClearContents
End If
Next r
ws.Range("C2:C" & lastRow).NumberFormat = "m/d/yyyy"
End Sub
The macro uses CLng to convert the offset to a whole number, so do not use it unchanged if fractional offsets have meaning in your data. Also note that it finds the final row by looking at column A. If your dates have gaps or another column determines the list length, adjust that last-row calculation. For rows containing Excel error values, add explicit error handling before calling IsDate or IsNumeric.
When a worksheet formula is enough
You may not need VBA if the goal is simply to calculate dates in cells. With starting dates in A2:A20, enter this in B2 and fill it down: =A2+7. If the number of days is in C2, with the starting date in A2, use =A2+C2. These formulas recalculate when their input cells change and avoid enabling macros. Format the result cells as dates if Excel displays serial numbers instead.
VBA is a better fit when the calculation is part of a larger automated process, such as writing values to another sheet, processing a batch, or running a procedure from a button. A formula keeps the logic visible in the grid and updates automatically. Decide whether the result should remain a formula or become a fixed value: the macro examples write values, so later changes to the source date will not update an already calculated result.
Dates, times, and display formats
A date cell can also contain a time. Adding a whole number of days preserves the time portion: 9:30 a.m. remains 9:30 a.m. on the resulting date. If you want a date-only result, remove the time deliberately, for example by applying VBA’s DateValue to the starting value before adding days. Do not strip the time if it is meaningful to the task, such as a scheduled appointment.
Number formatting changes what a cell displays, not the underlying date calculation. The format m/d/yyyy shows a month, day, and four-digit year, while a format such as dd-mmm-yyyy displays a readable abbreviated month. A cell showing a number after the macro may still contain the correct date; apply a date number format rather than changing the calculation.
Excel workbooks can use the 1900 or 1904 date system, and the systems assign different serial numbers to the same calendar date. VBA’s date functions work with date values, but be cautious when transferring raw numeric serials between workbooks configured with different systems. Prefer transferring actual dates through Excel’s date-aware values and verify the workbook’s date-system setting when copied dates appear shifted.
Calendar days versus business days
“Add five days” usually means five calendar days, including Saturday and Sunday. Neither DateAdd("d", 5, dateValue) nor ordinary date-plus-five arithmetic skips weekends. If a Friday is advanced by five calendar days, the result is Wednesday.
Business-day calculations have extra rules: which weekdays count, whether holidays are excluded, and what to do when the starting date is not a workday. Excel worksheet functions such as WORKDAY can handle weekday and supplied holiday dates in formulas, but simple VBA date addition does not apply those rules automatically. For a VBA process, you can call an appropriate worksheet function or write explicit business-calendar logic; validate it against the organization’s holiday list. Do not label a calendar-day result as a working-day deadline.
Common errors and how to avoid them
- Wrong cell or sheet: A macro using
Range("A2")without qualifying the worksheet may act on whichever sheet is active. Use a worksheet variable, as in the examples. - Text that only looks like a date: Imported text can be interpreted inconsistently or fail
IsDate. Convert and validate imported values before doing date arithmetic. - Unexpected number display: The result may be correct but formatted as General or Number. Set the destination cell’s
NumberFormatto a date format. - Negative or fractional offsets: Negative whole numbers are valid for moving backward. Decide how decimal offsets should be treated rather than letting an implicit conversion determine the outcome.
- Dates outside supported limits: VBA Date values have a finite supported range. Adding a large number near the limit can cause an overflow error, so check the expected range when offsets come from user input.
When debugging, inspect the input cell’s value and format, then confirm the macro is referring to the expected workbook and worksheet. If a result is off by one day, check for hidden time components, ambiguous text dates, or a date-system mismatch before changing the arithmetic.
Saving and running VBA safely
Before running a macro that edits cells, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z, so a backup is the straightforward way to recover the original data. Test on a small sample first, especially if the procedure writes into a range that already contains information.
To keep VBA code, save the workbook in a macro-enabled format such as .xlsm. Excel for Microsoft 365 on Windows supports VBA in the desktop app; Excel for the web can open a macro-enabled workbook but cannot create, run, or edit its VBA macros. Excel for Mac also supports VBA in the desktop app, although available shortcuts and some VBA capabilities can differ. On Mac, use Developer > Visual Basic; Option+F11 is a common editor shortcut, but some keyboards require the Fn key as well.
Frequently Asked Questions
How do I add one day to a date in VBA?
Use DateAdd("d", 1, dateValue) or add 1 directly to the date value. Both advance a valid date by one calendar day.
How do I subtract days from a date in Excel VBA?
Pass a negative number to DateAdd, such as DateAdd("d", -10, dateValue), or subtract 10 from the date value. The result is ten calendar days earlier.
Does DateAdd skip weekends?
No. The "d" interval counts calendar days, including weekends and holidays. For working days, use a business-day method with explicit weekend and holiday rules.
Why does my VBA result show a number instead of a date?
Excel stores dates as serial values, and the cell may be formatted as General or Number. Apply a date number format to the result cell; formatting changes its display, not its underlying value.
Can I add days without a macro?
Yes. If A2 contains a date, use =A2+7 in another cell to show the date one week later. A worksheet formula is often simpler when no other automation is needed.
Related
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Find and Replace in a Range: Code and TipsTo find and replace text only within a chosen Excel range, use VBA’s Range.Replace method and call it…
- VBA Break Function: How to Pause or Stop a Running MacroVBA does not have a built-in Break function or statement. To interrupt a running macro, use the Visual…
- Excel VBA Compile Error: Next Without For ExplainedIn Excel VBA, “Next without For” means VBA found a Next statement that it could not match to…
- Excel VBA Function Return Value: Syntax and ExamplesAn Excel VBA function returns a value by assigning that value to the function’s own name before the…
- Excel VBA Copy Paste Transpose: Working Code and TipsTo copy a range and paste it transposed with Excel VBA, copy the source range and use PasteSpecial…
- Excel VBA UserForm Cancel Button: Setup and BehaviorTo make an Excel VBA UserForm button act as Cancel, set that CommandButton’s Cancel property to True, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.