changing a date from a general text format from an excel file to date format
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
IDCORPCLINIC | DATESTART | DATEEND |
11029 | 42975 | 45217.0431 |
5305 | 40933 | 45214.03859 |
3291 | 39159 | 45213.01197 |
9234 | 44615.63681 | 45213.01197 |
2045 | 38202 | 45210.02734 |
11805 | 43451 | 45210.02734 |
1706 | 38459 | 45206.01279 |
2538 | 39145 | 45206.01279 |
12537 | 42955 | 45205.01203 |
1526 | 37566 | 45199 |
11952 | 44638.66698 | 45192.15895 |
2079 | 41151 | 45191.03427 |
want the export to look like
IDCORPCLINIC | DATESTART | DATEEND |
11029 | 8/28/2017 | 10/18/2023 |
5305 | 1/25/2012 | 10/15/2023 |
3291 | 3/18/2007 | 10/14/2023 |
9234 | 2/23/2022 | 10/14/2023 |
2045 | 8/3/2004 | 10/11/2023 |
11805 | 12/17/2018 | 10/11/2023 |
1706 | 4/17/2005 | 10/7/2023 |
2538 | 3/4/2007 | 10/7/2023 |
12537 | 8/8/2017 | 10/6/2023 |
1526 | 11/6/2002 | 9/30/2023 |
11952 | 3/18/2022 | 9/23/2023 |
2079 | 8/30/2012 | 9/22/2023 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I am getting this error when following the above instructions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
