MS-Excel / Functions and Formula

Using the DateDiff Function to Return an Interval

The DateDiff function returns the interval (the number of days, weeks, hours, and so on) between two specified dates. The syntax for DateDiff is as follows:

`DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])`

Here are the components of the syntax:

• interval is a required String expression giving the unit in which you want to measure the interval: yyyy for year, q for quarter, m for month, y for the day of the year, d for day, w for weekday, ww for week, h for hour, n for minute (because m is for month), and s for second.
• date1 and date2 are the dates between which you're calculating the interval.
• firstdayofweek is an optional constant specifying the day that starts the week (for date information). The default setting is vbSunday (1), but you can also set vbMonday (2), vbTuesday (3), vbWednesday (4), vbThursday (5), vbFriday (6), vbSaturday (7), or vbUseSystem (0; uses the system setting).
• firstweekofyear is an optional constant specifying the week considered first in the year. Table below shows the options for this constant.
The options for the Firstweekofyear constant
```Constant 	Value 	Year Starts with Week
VbUseSystem 	    0 	Use the system setting.
vbFirstJan1 	    1 	The week in which January 1 falls
(the default setting).
vbFirstFourDays     2 	The first week with a minimum of four
days in the year.
vbFirstFullWeek     3 	The first full week (7 days) of the year.
```

For example, the following statement returns the number of weeks between June 3, 2009, and September 30, 2009:

`MsgBox DateDiff("ww", "6/3/2009", "9/30/2009")`

The DateAdd function lets you easily add an interval of time to, or subtract an interval of time from, a specified date, returning the resulting date. The syntax for DateAdd is as follows:

`DateAdd(interval, number, date)`

Here are the components of the syntax:

• interval is a required String expression giving the unit of measurement for the interval: yyyy for year, q for quarter, m for month, y for the day of the year, d for day, w for weekday, ww for week, h for hour, n for minute, and s for second.
• number is a required numeric expression giving the number of intervals to add (a positive number) or to subtract (a negative number). If number isn't already of the data type Long, VBA rounds it to the nearest whole number before evaluating the function.
• date is a required Variant/Date or literal date giving the starting date.

For example, the following statement returns the date 10 weeks from May 27, 2010:

`DateAdd("ww", 10, #5/27/2009#)`