We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
alteryx Community

# Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
###### IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

## Challenge #10: Date Time Calculations

Alteryx Alumni (Retired)

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.

11 - Bolide

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.

Alteryx Alumni (Retired)

A solution to this exercise has been posted.

Tara McCoy
11 - Bolide

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

17 - Castor

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.

20 - Arcturus

I'm off to basecamp :)

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
17 - Castor

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.

ACE Emeritus

My solution. :)

Spoiler
11 - Bolide

Challenge #10 done in one tool!!

Spoiler
15 - Aurora

also did this with one formula tool