topic Re: Calculating # hours between dates in Alteryx Designer Desktop Discussions

Calculating # hours between dates

paigeblackstone — Wed, 20 Jun 2018 15:49:08 GMT

Hi everyone!

I have a data set with two date columns, one being inquiry opened date and the other being inquiry closed date. Both are DateTime data types. What I would like to do is calculate the number of hours elapsed for each row, between the two columns. Does anyone know of a way to do this? I was trying to write a formula but you can't really subtract dates and times, supposedly, so now I am stuck.

Any help you can provide is greatly appreciated. Thanks in advance!

Re: Calculating # hours between dates

Kenda — Wed, 20 Jun 2018 15:52:29 GMT

Hey @paigeblackstone!

You can actually find the difference between dates and times using the DateTimeDiff(dt1,dt2,u) formula. For more information on this, go to this Alteryx article: https://help.alteryx.com/current/Reference/DateTimeFunctions.htm

Hope this helps! Let us know if you still need more guidance.

Re: Calculating # hours between dates

BenMoss — Wed, 20 Jun 2018 15:53:29 GMT

Within the formula tool there is a syntax that allows you to do this...

DateTimeDiff([dt1],[dt2],units)

In your instance units would be 'hours'

Then your two date time fields would be dt1 and dt2.

Ben

Re: Calculating # hours between dates

paigeblackstone — Wed, 20 Jun 2018 15:57:28 GMT

hi @barnesK and @BenMoss,

does it matter which date is entered first? Should the later date be listed first or will Alteryx still understand that you need the absolute difference between the two?

This seems to be exactly the solution I was looking for!

Thank you

Re: Calculating # hours between dates

BenMoss — Wed, 20 Jun 2018 16:01:02 GMT

From the help.

"Subtract the second argument from the first and return it as a duration"

So I think your start date would be the 2nd argument, and your end date the first argument. I always get them the wrong way round but it's very easy to correct!

Re: Calculating # hours between dates

paigeblackstone — Wed, 20 Jun 2018 16:24:06 GMT

@BenMoss

Makes sense!

Okay so I ran it, however I am getting the error

"the formula resulted in a number but the field is a Date/Time. (Expression #1)."

Do you know what this might be caused by?

Re: Calculating # hours between dates

Kenda — Wed, 20 Jun 2018 16:51:46 GMT

@paigeblackstone Is the field you're creating a number (int, double, etc.) field? As the article states, "The duration is returned as a number, not a string, in the specified units." So you need to make sure that whatever field you're using the expression in is a numerical field.