DateDiff Function

Format

i = datediff(start_date, end_date ʃ,diff_typeʅ);

Description

The datediff function returns the difference between two dates as a number, defaulting to the number of days between the dates. This function acts similarly to Microsoft Excel's datedif function. The numeric expressions start_date and end_date must be in YYYYMMDD (year/month/day) format. If no year is present then the current or next year is assumed in order to satisfy the condition that the start date is earlier than the end date. If years are present, it is possible for the start date to be later than the end date, in which case the function returns a negative difference. An optional string expression, diff_type, indicates the type of date difference requested, and can be one of the following values:

diff_type	Description
"d"	days between the start and end dates (default)
"w"	weeks between the start and end dates
"m"	months between the start and end dates
"y"	years between the start and end dates
"md"	days between the start and end dates ignoring both the years and months of the dates
"ym"	months between the start and end dates ignoring the years of the dates
"yd"	days between the start and end dates ignoring the years of the dates

Return Value

The function returns the requested difference in dates, defaulting to the number of days between the dates. If the dates cannot be processed, the function returns default. The function returns values equal to Excel's function for most arguments, but due to processing differences involving leap years, sometimes CSPro's function will return a value different from Excel's.

Example 1

datediff(19790404, 19820605); // returns 1158
datediff(19790404, 19820605, "d"); // returns 1158
datediff(19790404, 19820605, "w"); // returns 165
datediff(19790404, 19820605, "m"); // returns 38
datediff(19790404, 19820605, "y"); // returns 3
datediff(19790404, 19820605, "md"); // returns 1
datediff(19790404, 19820605, "ym"); // returns 2
datediff(19790404, 19820605, "yd"); // returns 62

Example 2

numeric date1 = 20090120;
numeric date2 = 20121106;

string outputText = "The difference between January 20, 2009 and November 6, 2012 is %d years, %d months, and %d days";

errmsg(outputText, datediff(date1, date2, "y"), datediff(date1, date2, "ym"), datediff(date1, date2, "md"));

// returns ... 3 years, 9 months, and 17 days