How do I use datetimediff in Alteryx?
Category:
Question ID: 109587
0
0

I’m trying to find the difference between two dates in Alteryx.  In Excel, I’d just subract one from the other, but I don’t think that works here.

Am I supposed to use datetimediff in a Formula tool or something else?

Marked as spam
Posted by (Questions: 7, Answers: 1)
Asked on June 8, 2020 8:50 am
2366 views
Answers (1)
1
Private answer

You're right!  You cannot simply subtract one date from another, because date calculations require that you specify units.  Are you subtracting by days?  Years?  Minutes? Alteryx will not assume an answer to this.

The easiest way to subtract one date from another is to use the DateTimeDiff function in a Formula tool.  It can be a little tricky at first.  Here's how you do it:

  1. Make both fields the same type (Date vs DateTimeDiff).
  2. Set the later date first in the function.
  3. Subtract by the smallest (reasonable) unit, and work upward from there.

This gives us a syntax of DateTimeDiff([Later Date],[Earlier Date],"[unit of time]")

So, for example, let's say I have two Date fields: [BirthDate] (my birthday) and [BestDayOfTheDecade] (the day the Atlanta Braves won the World Series).  If I want to know how old I was on that day in years, I need to calculate by days, set the field type to a float or double, and divide by 365.25 to get years.

The formula here would be (DateTimeDiff([BestDayOfTheDecade],[BirthDate],'days'))/365.25

Why not just use 'year' as the unit?  Because the result of that function is an integer (rounded down), and we want to be exact.  Here are the two results, assuming I was born on April 1, 1980, and the Braves won the World Series on October 28, 1995 (spoiler: they did):

  1. Setting units = 'year': DateTimeDiff = 14 years
  2. Setting units = 'day': DateTimeDiff = 14.75 years

That 0.75 can make a huge, huge difference over many records.  So again, use the smallest unit you can, and work upward from there.

We'll go more in-depth on the DateTimeDiff function on the Orasi blog pretty soon.  In the meantime, did this help?  Let us know over at https://analytics.orasi.com/contact/.

And, if you want to relive the greatest moment of the 1990's, you can do that here.

Marked as spam
Posted by (Questions: 0, Answers: 5)
Answered on June 18, 2020 9:12 am
EyeOnTesting

Welcome back to "EyeOnTesting" brought to you by Orasi Software, Inc.

X
Scroll to Top