This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
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
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.
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.