community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

Calculating # hours between dates

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!

Pulsar
Pulsar

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.

Alteryx Certified Partner
Alteryx Certified Partner

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

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

Alteryx Certified Partner
Alteryx Certified Partner

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!

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

Pulsar
Pulsar

@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