Converting an inconsistent number to date

[Brittwisch]

Hello, I have a field of dates that are inconsistent lengths, such as 50124 or 120124 for May 5, 24 and Dec 1, 24, respectively. Is there a way to dynamically change these to a date in alteryx as it will be different with each months data pull?

[ChrisTX]

If you had a value like 12524 would it be

   January 25 or December 5?

Can you always assume 2 digits for the year and 2 digits for the day?

Please provide rules about what formatting can be relied upon. 

Chris

[AGilbert]

Here is an idea to append a leading 0 when necessary. Based on your two examples, this solution is assuming that the strings will always have 2 digits for day and year (e.g. MDDYY or MMDDYY).

I thought that the %m specifier would handle this case so I'm curious what other solutions are offered. In any event, the datetime specifier documentation here is something work bookmarking in your browser. 

[Brittwisch]

There is one or two digits for month, always two digit day, and two digit year

[Yoshiro_Fujimori]

Hi @Brittwisch ,

I assume 50124 is "May 1, 2024" instead of "May 5, 24".

Then the expressions below should work.

yyyy = ToString(Mod([Field1], 100) + 2000)

mm = PadLeft(ToString(FLOOR([Field1] / 10000)), 2, "0")

dd = PadLeft(ToString(Mod([Field1] / 100, 100)), 2, "0")

Date = ToDate([yyyy] + "-" + [mm] + "-" + [dd])

Date2 = DateTimeFormat([Date],"%b %e, %y")

Output

Field1	yyyy	mm	dd	Date	Date2
50124	2024	05	01	2024-05-01	May 1, 24
120124	2024	12	01	2024-12-01	Dec 1, 24

I hope this helps.

[Yoshiro_Fujimori]

This may be simpler.

String = PadLeft(ToString([Field1]), 6, "0")

Date = ToDate( "20" + Right([String], 2) + "-" + Left([String], 2) + "-" + Substring([String],2,2) )

Date2 = DateTimeFormat([Date],"%b %e, %y")

[ChrisTX · Accepted solution]

@Brittwisch use a Formula tool.  This should point you in the right direction:

 

Here's a list of all Functions:  Functions (alteryx.com)

If your current data type is a number, convert it to a string with the function ToString or a Select tool.

Use the function Trim to ensure there are no leading or trailing spaces.

Use the function PadLeft to add a leading zero, if needed, to ensure the total string length is 6.  This will add a 0 in front of 50124.

Use the function DateTimeParse with the correct specifiers.  See DateTime Functions (alteryx.com)

Chris