Alteryx Designer Desktop Discussions

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

changing a date from a general text format from an excel file to date format

Ksisterhen
8 - Asteroid

Hi-

 

I have a file that brings in the dates like this.  to change them to show as a date format in excel, i would just change the format in the tool bar to date.

 

How can i change the format to date in alteryx if it comes in like this.

 

input 

IDCORPCLINICDATESTARTDATEEND
110294297545217.0431
53054093345214.03859
32913915945213.01197
923444615.6368145213.01197
20453820245210.02734
118054345145210.02734
17063845945206.01279
25383914545206.01279
125374295545205.01203
15263756645199
1195244638.6669845192.15895
20794115145191.03427

 

want the export to look like 

IDCORPCLINICDATESTARTDATEEND
110298/28/201710/18/2023
53051/25/201210/15/2023
32913/18/200710/14/2023
92342/23/202210/14/2023
20458/3/200410/11/2023
1180512/17/201810/11/2023
17064/17/200510/7/2023
25383/4/200710/7/2023
125378/8/201710/6/2023
152611/6/20029/30/2023
119523/18/20229/23/2023
20798/30/20129/22/2023
6 REPLIES 6
JosephSerpis
17 - Castor
17 - Castor

Hi @Ksisterhen use the Todate formula in combination with Datetimeformat formula to get your dates into MM/DD/YYYY format. If you use a Multi-Field Formula tool you can apply the same function to multiple columns.

 

Date_18102023.JPG

SPetrie
13 - Pulsar

You can use todate to convert the decimal date to a date/time format and then format it to your liking.

 

datetimeformat(todate([_CurrentField_]),"%m/%d/%Y")

date.PNG

Ksisterhen
8 - Asteroid

I am getting this error when following the above instructions

 

 

SPetrie
13 - Pulsar

Where there any warnings about numbers being beyond the max or minimum?

Thats the only thing I can think that would cause that issue. If the number was beyond the range of an acceptable decimal format date, it wouldn't convert and stay as a number which would cause the format portion of the formula to give that error.

Are you able to share the data for those columns making the error?

Ksisterhen
8 - Asteroid

see attached, also how can i add a column that notes that if there is an order event that contains signed and the event type date was in the past year it should say signed in a new column i.e for order 5

 

thanks!

SPetrie
13 - Pulsar

The example data all converted with no issues for me. I altered the formula to use the original value if it fails to convert to hopefully make it easier to see where the issue is.

As for a new column, that should be pretty straight forward. Setup a formula to look for items that contain "signed" and have a date that is greater than or equal to today-1year.

The only real option then would be if you wanted it only on the line items that passes that test, or if you want all line items of that order to have the note.

I included both version in the attached workflow.

v1.PNGv2.PNG

Labels
Top Solution Authors