Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Date time difference, output as Days and hours

Highlighted
8 - Asteroid

Difference can be calculated as (grossdatetimeout  - taredatetimein)

 

 

taredatetimeingrossdatetimeout
3/5/2019 9:033/6/2019 7:20
3/7/2019 7:093/7/2019 13:45
3/8/2019 7:213/8/2019 19:05
3/9/2019 12:163/11/2019 13:53
3/11/2019 21:573/12/2019 15:19
3/13/2019 18:503/14/2019 19:24
3/15/2019 11:263/15/2019 20:18

 

Output should be days and hours. If the difference is above 24, then it can be calculated as days and it is below 24 can be mentioned as hours in the same column.

 

example: 1 day 11 hours

 

I tried to achieve through Datetimediff, but not getting the expected results.

 

Is there any other alternative?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@pgayath1 ,

 

I converted the input to ISO datetime formats then calculated both hours and days.  For hours, I used a mod() function to find the hours left over when you divide the result by 24 (e.g. Mod(25,24) = 1).  I then concatenated the strings with conditional logic so that 1 hour reads without 1 hours.

 

capture.png

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
9 - Comet

Hi, you can use DateTimeDiff, but you need to calculate the days and hours separately and string them together at the end:

tonyp_0-1581511452283.png

I turned the DateTimes into DateTime format fields then used these four formula:

Hours diff - calculates the total hours difference

Days Diff - Divides the total hours by 24 to get the number of days (keep this as Int16 for whole days only)

Hours only - Total hours minus (total days x 24) gives you the left over hours

Display Diff - turns the days and hours to strings so you can format it with 'days' and 'hours' text around the numbers. If Hours only = one is displays  'hour' instead of 'hours'

 

tonyp_2-1581511852337.png

 

Hours Diff: DateTimeDiff([GrossDateTimeOut],[TareDateTimeIn],'hours')

Days Diff: iif([Hours Diff]/24 < 1, 0, [Hours Diff]/24)

Hours only: [Hours Diff]-([Days Diff]*24)

Display Diff: ToString([Days Diff],0)+' days '+ToString([Hours only],0)+
iif([Hours only]=1,' hour',' hours')

 

Returns: 

tonyp_3-1581511972036.png

 

 

 

 

Highlighted
Alteryx Certified Partner

Here my humble solution

 

Highlighted
8 - Asteroid

 

I am getting [Null] as output. am I missing anything

taredatetimein             grossdatetimeout
2018-07-04 06:55:03 2018-07-04 14:22:22
2018-07-06 06:46:29 2018-07-06 14:43:57
2018-08-04 07:11:50 2018-08-04 16:53:01
2018-08-14 06:58:23 2018-08-14 13:20:36
2018-08-18 07:02:17 2018-08-18 15:23:37
2018-08-25 07:44:46 2018-08-25 15:22:44

Labels