**Question ID:**109587

♥ 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 |

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? 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: - Make both fields the same type (Date vs DateTimeDiff).
- Set the later date first in the function.
- Subtract by the smallest (reasonable) unit, and work upward from there.
This gives us a syntax of 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): - Setting units = 'year': DateTimeDiff = 14 years
- 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 |