Challenge #34: Date-Time Formatting

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 #33 is HERE. For this chalenge let’s look at creating a macro from scratch. I recommend you first solve the problem with tools on the canvas, then cake a macro from the tools.

Use Case: Our customer has a need to convert date/time strings into a date-time format.

Examples of the different input formats include: 4/8/2015 4:00, 5/10/2015 13:00. The conversion is automatic when the hours are 2 digits (10-24), but it ignores hours 1-9 (creates NULL values on conversion).

Objective: Create a macro to effectively convert and preserve the data in a date-time format.

challenge_34_start_file.yxmd

challenge_34_solution.yxzp

Parse

Macros

Preparation

Advanced

Interface

Difficult

Accepted answers

All comments

BenChan

Thank you for posting this amazing exercise.

I am new to Alteryx community. Does anyone know how to upload my yxmd and yxmc files onto Alteryx Community?

Here is my smiling-face macro:

Here is what's inside my smiling-face macro:

Spoiler

MattD

Here's a solution:

Spoiler

SeanAdams

nice quick one.

Solution attached - very similar to the provided solution

challenge_34_SeanSolution.yxzp

MarqueeCrew

I think that this is straight-forward (after finding the reverse update).

Cheers,

Mark

challenge_34_MarqueeCrew.yxmc

NicoleJohnson

My solution.... in a single formula!! This might be the first time I've had a one-tool solution!!

Spoiler

Formula Tool = DateTimeParse(Left([date_time],FindString([date_time]," "))+" "+PadLeft(Substring([date_time],FindString([date_time]," ")+1,8),5,"0"),"%m/%d/%Y %H:%M:%s")

challenge_34_NicoleJohnson.yxmc

estherb47

My solution. Took a number of tries to get everything right.

DateConvertExercise.yxmc

LordNeilLord

I think the datetime convert maybe my favourite tool...so easy and simple

Spoiler

challenge_34_LNL.yxzp

nick_ceneviva

Solution Attached.

Challenge34_Ceneviva.yxzp

patrick_digan

Spoiler

challenge_34_start_file.yxzp

JoshKushner

Love all this practice with Regex. These weekly challenges trained me to regex in my sleep now. (seriously)

challenge_34_start_file.yxzp

Natasha

I either don't get this challenge or it is outdated. Anyway here is my solution

Spoiler

1) Currently the date-time converter tool handles both 01 and 1 records of date and time
2) I don't see the reason for creating a macro

Screen Shot 2017-11-12 at 15.43.23.png

challenge_34_Natasha.yxmd

challenge_34_Macro.yxmc

samN

Macros and regex!? Must be my lucky day

Forgot to attach my Macro the first time

challenge_34.yxzp

ggruccio

Quick and easy macro solution!

challenge_34_gg_finish.yxzp

philipmannering

Tried two solutions. And realised that there's no need to pad when using the datetime format tool. Solution attached.

challenge_34 - Snip.PNG

challenge_34 - DateTime Parse.yxmd

LandonG

Solution attached.

challenge_34_start_file.yxzp

jasperlch

Solution attached.

challenge_34_JL.yxzp

msicak

conquered my fear of macros. for now.

Spoiler

challenge_34_MS.yxmc

challenge_34_MS.yxmd

jamielaird

Here's my solution.

Spoiler

challenge_34_solution_JL.yxzp

Waynemk

here is the macro

challenge_34_waynek.yxmc

dsmdavid

I think the single tool would work, but I am trying to get my head around iterative macros and regex so...

Spoiler

solved#34.yxzp

JosephSerpis

Challenge Completed

Challenge_34_Joe_Serpis.yxzp

CHarrison

My solution

Challenge 34.yxmd

asabau

Here it is!

challenge_34_datetimemacro.yxmd

unknown

challenge_34_ryan_lambert.yxmd

kcoulon

I did this a little differently than the provided solution, but it seems to work!

challenge_34_start_file.yxmd

Challenge 34 macro.yxmc

svenk

My Solution!

DateTime.yxmc

justynam

My solution :-)

challenge_34_solution_JM.yxzp

Laurap1228

Here is my solution. Short and sweet

challenge_34_LP.yxzp

tcroberts

Here's my solution, although perhaps there's been an Alteryx update that has invalidated this challenge, since I feel like I must be missing something:

Spoiler

Initially, when trying to tag the DateTime strings, I had the following regular expression:

[0-][0-9]?/[0-9][0-9]?/\d\d\d\d \d:\d\d

which tagged all rows which had single-digit hours.

However, it turned out that everything worked when I simply passed it through the DateTime tool, and I was able to add the leading 0 by simply passing it back through with a custom DateTime format.

challenge_34_datetimeworkflow.yxzp

Ruud

If it's ugly and it works, it is still ugly.

challenge_34_ruud.yxmc

CaraI

In Version 11.0 the Date-Time Parse tool was upgraded, making the need to use more than one tool obsolete for this challenge. However, in the spirit of the weekly challenge, I did it both ways.

Spoiler

challenge_34_CaraI.yxmc

RobertoEstrada

I recommend that you export your workflow as a package so you can upload your entire solution.

jacob_kahn

@NicoleJohnson THIS IS AMAZING. Just saying.

TulsiM

my solution

Baran

Nice and easy!

challenge34macro_baran.yxmc

challenge34_baran.yxmd