Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Changing Date Format Coming From Data

mlail
6 - Meteoroid

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!

10 REPLIES 10
estherb47
15 - Aurora
15 - Aurora

Hi @mlail ,

 

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

image.pngimage.png

 

Cheers!

Esther

CharlieS
17 - Castor
17 - Castor

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
6 - Meteoroid

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

CharlieS
17 - Castor
17 - Castor

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

mlail
6 - Meteoroid

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
17 - Castor
17 - Castor

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. 

 

20190213-ExcelDateFormat.png

 

 

 

 

 

 

 

 

 

 

 

20190213-ExcelDateFormat2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

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

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

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