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.
Ok, so I have what I think is a simple question but it's about driven me mad at this point.
I have data coming into Alteryx in MM/DD/YYYY format. I want to write the output to excel in the same format and format the field as a date as opposed to text. The reason I want the field formatted as a date is that this file gets imported into another system and it requires the field be in date format. Obviously, I could bring the date in as text and output to text and then convert the field to date in Excel, but I would prefer to do this in Alteryx.
I've tried using the date/time parse tool and the only thing I've gotten out of that is frustration. If anyone could give me advice on how I should bring in my date field and in what order I should perform the conversions, it would be much appreciated!!
Will the attached solution help? I used the DateTime tool by specifying the format of incoming string field as "MM/DD/YYYY" --> the formatted date is automatically transformed into a Date format, and when written to Excel it's also automatically formatted as "MM/DD/YYYY" in date format. See screenshot as attached.
I appreciate all the quick replies. I understand the recommendations to use the Date/Time parse tool, but something still isn't working for me. I'm limited with what I can share, so going to provide as many details here as I can:
Input file = .csv, the dates are formatted as dates within the .csv file
Within the workflow, one of the first tools is a select. I select all of those date fields, when I select them, what format should I be using?
Currently if I select date and run the workflow, all of my date fields are blank in the output. I see field conversion errors: "maturity_date: "4/27/2025" is not a valid date"
I just tried adding adding date/time parse tools inbetween my input and select tools, each one used 'String to Date/Time format' and the 'MM/dd/yyyy' format. I'm still getting the same error in the output though.
I'm not sure I understand why, if my field is formatted as a date in my input data, and I use a select and declare the field a date there, why would I get any errors? I'm not transforming or doing any calculations with the date.
That makes sense. I think I realize the source of the error now as well, my date isn't actually in MM/DD/YYYY format, it's not that standardized. The errors I'm getting are related to dates that truncate the leading zero (any month less than 10 and any day less than 10, 7/1/2019 for example).
It sounds like I can select these fields as strings and just before writing my output, I would use the date/time converter to convert to a date. But is there a format that will handle dates like I described in the first paragraph?
I'm attaching more screenshots because it's still not working. I've tried this just about every way I know how and I'm still baffled.
The end of my workflow uses a select to order and rename columns immediately before writing the output, here is a screenshot.
I'm still getting all sorts of conversion errors. I'm bringing the fields in as strings, converting them where highlighted in the screenshot and then selecting as a date in the final select. What am I doing wrong?