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")

Using the DateAdd Function to Add to a Date

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#)
[Previous] [Contents] [Next]

In this tutorial:

  1. Using Built-in Functions
  2. What Is a Function?
  3. Passing Arguments to a Function
  4. Using Functions to Convert Data from One Type to Another
  5. Using the Asc Function to Return a Character Code
  6. Using the Str Function to Convert a Value to a String
  7. Using the Format Function to Format an Expression
  8. Using the Chr Function and Constants to Enter Special Characters in a String
  9. Using Functions to Manipulate Strings
  10. Using InStr and InStrRev to Find a String within Another String
  11. Using LTrim, RTrim, and Trim to Trim Spaces from a String
  12. Using Len to Check the Length of a String
  13. Using the StrComp Function to Compare Apples to Apples
  14. Using VBA's Mathematical Functions
  15. Excel VBA's Date and Time Functions
  16. Using the DateDiff Function to Return an Interval
  17. Using File-Management Functions