cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

###### #SANTALYTICS

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

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.

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

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

Highlighted
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

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!

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

@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