Challenge #10: Date Time Calculations

[GeneR]

Here is this week’s challenge, I would like to thank everyone for playing along and for your feedback. The link to the solution for last challenge #09 is HERE. For this challenge let’s look at the date and time functions in Alteryx.

The use case:
A distribution center receives a package. At the point of receipt, the package is scanned and a timestamp is captured for arrival date/time. The company is trying to reduce the amount of time the package is at its facility and as a result is trying to analyze how long packages remain at the facility.

The objective for this challenge is to calculate the delta between arrival date/time and the Time_Now field (this field has the date/time of the creation of this challenge and will be changing as time goes on). Create a unique field for Days, Hours, Minutes and Seconds.

We have listed this as an intermediate exercise  and I expect it will go very quickly for many of you. Let us know what you think, we are looking forward to hearing your feedback.

UPDATE 2/1/2016

You can find the solution below.

[alex]

Just a heads up, since the Time_Now stamp is slightly different in the input file vs. the output file, 2014-07-14 08:24:35 vs. 2014-07-14 08:25:16, the answers will be off by 41 seconds if you are verifying results against the output file.

[TaraM]

A solution to this exercise has been posted.

[alex]

I went with the following formulas and solved it correctly using one less formula than the posted answer. 

Spoiler

Days = DateTimeDiff([Time_Now],[TIMESTAMP],"days")

Hours = DateTimeDiff([Time_Now],[TIMESTAMP],"hours")-[Days]*24
Minutes = DateTimeDiff([Time_Now],[TIMESTAMP],"minutes")-DateTimeDiff([Time_Now],[TIMESTAMP],"hours")*60
Seconds = DateTimeDiff([Time_Now],[TIMESTAMP],"seconds")-DateTimeDiff([Time_Now],[TIMESTAMP],"minutes")*60

[mceleavey]

A simple modulo formula did the trick:

Spoiler

Here, I simply created a straight datetimediff calculation for the Days, and modulo differences for the others:

Hours - mod(DateTimeDiff( [Time_Now],[TIMESTAMP],"hours"),24)
Minutes - mod(DateTimeDiff( [Time_Now],[TIMESTAMP],"minutes"),60)
mod(DateTimeDiff( [Time_Now],[TIMESTAMP],"seconds"),60)




Simple.

[MarqueeCrew]

I'm off to basecamp :)

[SeanAdams]

Very similar to all the others

Spoiler

Didn't use a mod formula, but used the floor function to trim off the decimal places.

Also, broke it out a little to make the workings a little more obvious - used a set of raw fields to hold total days, hours, minutes, seconds for the entire span, and then just trimmed back from this using a floor function.

[NicoleJohnson]

My solution. :)

Spoiler

[Laurap1228]

Challenge #10 done in one tool!!

Spoiler

[estherb47]

also did this with one formula tool