Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculating # hours between dates

paigeblackstone
6 - Meteoroid

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!

6 REPLIES 6
Kenda
16 - Nebula
16 - Nebula

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.

BenMoss
ACE Emeritus
ACE Emeritus

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

paigeblackstone
6 - Meteoroid

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

BenMoss
ACE Emeritus
ACE Emeritus

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!

paigeblackstone
6 - Meteoroid

@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?

Kenda
16 - Nebula
16 - Nebula

@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.

Labels