Date Difference

NB. Most tools use different conventions for referring to date parts. For a full comparison of the different options, go to Appendix > DateTime Parts.

Excel

=~DATEDIF!~(‹start_date›, ‹end_date›, "‹date_part›")

=DATEDIF(A1, B1, "D")

NB.:

• DATEDIF reminder syntax is not listed in the usual prompt that appears when you start typing an Excel function.

• The ‹date_part› must be wrapped in double quotes or the formula will not calculate.

• Supported date parts are as follows (all fractions are rounded down):

Date Part	Equates to	Min : Max Values
"D"	Days	-∞ : ∞
"M"	Months	-∞ : ∞
"Y"	Years	-∞ : ∞
"MD"	Days (irrespective of month & year of the two dates)	0 : 30
"YD"	Days (irrespective of the year of the two dates)	0 : 364
"YM"	Months (irrespective of the year of the two dates)	0 : 11

Excel does not support differences in hours/minutes/seconds when using DATEDIF.

If you want to find a difference in time, you can use ~TEXT!~(‹end_time›, ‹start_time›, ‹format›) where ‹format› is one of 'h', 'h:mm', or 'h:mm:ss'.

Tableau

~DATEDIFF!~('‹datepart›', ‹start_date›, ‹end_date›«, start_of_week»)

DATEDIFF('day', [Transfer Date], [Finish Date])

NB. Supported date parts are as follows (all fractions are rounded up):

Date Part	Equates to
'year'	Years
'quarter'	Quarters
'month'	Months
'week'	Weeks
'day'	Days
'hour'	Hours
'minute'	Minutes
'second'	Seconds

Alteryx

~DATETIMEDIFF!~(‹start_date›, ‹end_date›, '‹date_part›')

DATETIMEDIFF([Transfer Date], [Finish Date], 'days')

NB. Supported date parts are as follows (all fractions are rounded down):

Date Part	Equates to
'year' / 'years'	Years
'month' / 'months'	Months
'day' / 'days'	Days
'hour' / 'hours'	Hours
'minute' / 'minutes'	Minutes
'second' / 'seconds'	Seconds

OrgVue

‹end_date›.~diff!~(‹start_date›«, date_part»)

node.transferdate.diff(node.finishdate,'d');
// OR
date(2016,10,18).diff(date(2015,11,23), 'm')

NB. Supported date parts are as follows (fractions are not rounded):

Date Part	Equates to
'y'	Years
'q'	Quarters
'm'	Months
'w'	Weeks
'd'	Days

tSQL

~DATEDIFF!~(‹date_part›, ‹start_date›, ‹end_date›)

SELECT DATEDIFF(month,TransferDate,FinishDate) AS ShippingPeriod
FROM OrdersData

NB. Supported date parts are as follows (fractions are not rounded):

Date Part	Equates to
year	Years
quarter	Quarters
month	Months
day	Days
week	Weeks
hour	Hours
minute	Minutes
second	Seconds
microsecond	Mircroseconds
nanosecond	Nanoseconds

Python

~datetime.date!~(‹end_date›) - ~datetime.date.!~(‹start_date›)

from datetime import date 
# so you don't have to reference datetime.date() to call each date

date(finishDate) - date.(transferDate)
# OR
date(2016,10,18) - date(2015,11,23)

This returns a date object. To unpack it as a number of days, wrap the difference expression in () and append .days:

from datetime import date

(date(2016,10,18) - date(2015,11,23)).days

NB.:

• This requires the datetime library to be imported.

• This returns the difference in days which can be divided/multiplied to give other units.

• When manually creating a date, days and months must be entered as a single digit when < 9 (with no padding '0'), e.g. date(2016,9,20).

• The above examples assume variables have been declared for finishDate and transferDate. For more information, go to the chapter General > Variables.