Returns the number of days, months or years between two date/time values.
DateDiff ( t, d1, d2 [, a] [, w] )
Time interval for comparison, e.g. years.
Day = 4
DayOfYear = 3
Hour = 7
Minute = 8
Month = 2
Quarter = 1
Second = 9
Weekday = 6
WeekOfYear = 5
Year = 0
||First date/time value for comparison. If this value is hard-coded must be prefixed and suffixed with a hash (#) character, e.g. #31/1/2006#.
||Second date/time value for comparison. If this value is hard-coded it must be prefixed and suffixed with a hash (#) character, e.g. #31/1/2006#.
||Optional – 1 to enforce the return value is positive, i.e. return absolute. 0 or leave out to return positive or negative values based on whether d1 is greater than d2. Note that you must include this parameter if you wish to use any following parameter.
||Optional – 1 to compare whole units (works for years or months). For example, in an age calculation based on date of birth and current date, if the subject has not had their birthday yet this year a simple DateDiff calculation would return a result 1 year older than their age. Setting this parameter to 1 will avoid this issue.
If the first date/time value is later than the second date/time, a negative difference will be returned. For this reason, the first date should be considered the start date, and the second date the end date. The Absolute parameter may be used to ensure the difference is always returned as a positive number.
Calculate a person’s age:
This example assumes there are 2 questions: a user prompt question (q1) which collects the person’s date of birth; and a variable question containing two answers, one for today’s date named “Today” and the other to perform the age calculation.
DateDiff(0, [q1], Today(), 1, 1)
The parameters passed in do the following:
||Time interval of “Year” to ensure we are comparing years between the dates.
||References the user prompt question with ID 1. This will contain the person’s date of birth – for the example we will assume this is 1st Jan 1970.
||Function returning today’s date
||Sets the Absolute option, so that we know we will always return a positive value regardless of the order the two dates are passed in
||Sets the Whole Units option, so that it eliminates the problem of just assuming the age is the difference of years
So in effect, after references have been calculated (assuming today is 1 Jan 2020) our example will appear as follows: DateDiff(0, #1-Jan-1970#, #1-Jan-2020#, 1, 1) and evaluate to 50.
Please note that the hash (#) character did not need to be provided in the original formula, as the system automatically adds those when resolving the references.
datediff today dateadd hash formula dateformat adddate
function format date age