Changing Date Format Coming From Data

[mlail]

Hello,

I have an example report that has been trimmed down to what i am looking at. The Planned date is formatted as the date and time i would like to know how to split the column into two one for the date and one for the time. Right now it is "Nov 4 00:32:" I would like it to be "11/4/2018" and "00:32" 

I've tried using the Date Time parsing tool and the text to columns tool as well. With no luck

Looking for some help.

I have attached the example report. 

Thanks!

[estherb47 · Accepted solution]

Hi @mlail ,

You can use 2 Date Time Parse tools to do this. The new fields will be text though, not dates or times.

Cheers!

Esther

[CharlieS · Accepted solution]

The easiest way to do this would be to use a Formula tool that creates new fields that changes the field types and does some minor formatting. Since the requested formats are not in the Alteryx/ISO 8601 format, they should be string values. 

Check out the attached workflow and let me know if you have any questions.

[mlail]

This is amazing - thank  you! Definitely will be used going forward :) 

[CharlieS]

I'm happy to help. Be sure to mark the post by @estherb47 as a solution too.

[mlail]

Sorry to bother - but i noticed while trying to build the formula into my workflow i saw that your Planned and Dispatch Date in the attached workflow was in a "prettier" format that what i have.

I see yours is formatted as Type: Date Time and looks like: 2018-11-04 00:32:00

While unfortunately my data is formatted as Type: V_String and looks like: Nov 4 00:32:

I tried throwing a select tool in and changing the Column type to Date and Time - data disappeared, i also tried do a convert date parse tool and the outcome columns were empty as well. I think it's from the source excel file i am using a macro to pull in many of these reports so changing the excel format is what i'm trying to avoid. 

any thoughts on where to go from here?

[CharlieS]

When you use an Input tool to bring the Excel file into Alteryx: are your fields are being formatted as strings? When I downloaded your file and viewed it in Excel, the data was stored in the ISO 8601 format "2018-11-01 00:32:00" and it was the cell formatting that displayed it as "Nov 4 00:32" The cell formatting is typically ignored and the stored data value is imported. 

[MarqueeCrew · Accepted solution]

I like the extra challenge of not knowing the year while converting the string to a date/time.  You can convert to 1400-11-04 00:32:00 with this formula:

datetimeparse([Field1],"%b %d %H:%M:")

You could modify the string to replace 1400 with the current year and if the result is in the future, you could replace with the prior year.  That messy and nested formula looks like this:

IF 
	Replace(datetimeparse([Field1],"%b %d %H:%M:"),"1400",ToString(DateTimeYear(DateTimeStart()))) <= DateTimeStart()
THEN
	Replace(datetimeparse([Field1],"%b %d %H:%M:"),"1400",ToString(DateTimeYear(DateTimeStart())))
ELSE
	Replace(datetimeparse([Field1],"%b %d %H:%M:"),"1400",ToString(DateTimeYear(DateTimeStart())-1))
endif

Cheers,

Mark

[CharlieS]

Once again, @MarqueeCrew shows us there's more than one way to a solution. Nice work.

[MarqueeCrew]

@CharlieS ,

This type of data challenge makes for an interesting diversion.  Once I see what looks to be a pattern (how I think about solving the issue in my mind), I begin to use Alteryx reference materials to assist me in the transformation.  I don't know if this is the end of @mlail 's challenge, but glad if it helps along the way.

Thanks for your quick star.

Cheers,

Mark