community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Issues formatting dates

Meteor

For some reason I have a real issue with dates in Alteryx.  We are not allowed to go directly to export data from our system of record so we must export to excel, all dates are strings when exported and they are formatted as mm/dd/yyyy.  One date in particular is in mm/dd/yyyy hh:mm:ss.  I have attached a portion on a workflow where I am trying to add a new date field that does not contain the hh:mm:ss.  The first formula is where I am adding a new date called "Date Time Copy".  I then have 2 Date/Time tools where I am trying to convert to dates.  Then the following formula is trying to convert the  "Date Time Copy" to only contain the mm/dd/yyyy.  Bottom line after the Date/Time tools both seen to change to Null. (Date Time Created2 and Date Created).

 

If someone could help me understand what I am doing wrong with using the Date Tool or even the formula, it might help me understand some of my other workflows where I just can't convert strings to date format and get then in the correct format of mm/dd/yyyy or without them coming out Null.  Some of my other workflows I need to go through 3 Date Time tools until I get the correct format. In some cases I really don't need them as dates (not doing any calculation) but would like to only change formats (formula tool?).

 

Any help would be appreciated!
Thank you.

I have attached a portion of my workflow - Owned Or Not By ECS Dates.yxzp

Alteryx Certified Partner

Hi surfside1!

 

A couple things for you:

 

1. I was able to open the workflow but it didn't have the excel file you are looking at (\\mns2nsf701z1\c_ehs_groups\DPC_ALL\Exceptions\ECS_Exception_Reporting\New_Format_Unfiltered_PolicyWorks\Unfiltered_Policyworks_new_format_06-26-2019_alteryx.xlsx) if you are able to attach that here I could help you work through this example

 

2. Without the file it's hard to validate if you indeed have selected the right incoming string to match. I particularly like to use @BarnesK datetime macro you can find here: https://gallery.alteryx.com/#!app/DateTime-Parse-KReW/5a9db7bb0462d72984db9271 it makes life so much easier!

 

Hope this helps!

Joshua

Bolide

Hey @surfside1,

 

In your post you describe your dates being in "mm/dd/yyyy hh:mm:ss" format, but in your workflow you have selected "mm/dd/yy hh:mm:ss", so it's expecting a 2-digit year when you have 4 digits. It's common for the tool to return nulls if you select the wrong format.

 

I recommend only using the DateTime tool to get the date from a string to a datetime, then do any other date formulas in a formula tool. The easiest way to remove the 'hh:mm:ss' is to simply put the datetime you want to convert in the formula, but make the data type a Date. This does require creating a new field, but if you find you have to do this often, you could turn it into a macro.

 

Capture.PNG

 

Best,

mmenth

Bolide

Using the DateTime Krew Macro that @joshuaburkhow suggested, I was able to convert all of your dates at once in a single tool. I recommend going to this link and downloading that so you can use it in your workflows going forward: https://gallery.alteryx.com/#!app/DateTime-Parse-KReW/5a9db7bb0462d72984db9271

 

Best,

Megan

Pulsar
Pulsar

Hi @surfside1 

When you're using the Date Time tool to convert from a String to a date, you need to specify the format that the actual string is in, rather than the format in which you want it to appear. So, since the year piece is 4 digits, you'd need to choose the MM/dd/yyyy hh:mm:ss format.

I've attached a workflow that leverages the multi-field formula tool to work with your date fields. Select whichever date fields you want to work with, and then use a Regex_Replace([_CurrentField_],"(.*?)($|\s.*)","$1") to remove the time stamp from the date.

If you want to then convert into a date, you can check off the option to change the field type to Date, and nest the Regex replace within a DateTimeParse function. DateTimeParse(Regex_Replace([_CurrentField_],"(.*?)($|\s.*)","$1"),"%m/%d/%Y")  Just like the Date Time tool, we have to let Alteryx know the format of the actual date, not the format we wish it appeared in.

Let me know if this helps

Cheers!

Esther

Labels