How to create correct Date Field
- 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
I have field in my Excel file which shows data in PERIOD field as "23-Jan" it means January month and year 2023 but when I export into Alteryx it shows up as 2024-01-23 which is wrong I want see data in month starting MM-DD-YYYY format that is 01-01-2023 and in another column I want to see data in month ending MM-DD-YYYY format that is 31-01-2023. Can anyone help me with it? here is the alteryx workflow and excel file. I want to create 2 Date fields.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @srk0609 ,
this workflow is bare. What have you tried so far?
There are a few options for both of these requirements. Given the few input rows, we have to make some assumptions, such as the century of the year given in the period is the 21st century.
You could connect a formula tool and use an expression like this:
'20'
+ PadLeft(ToString(DateTimeDay([PERIOD])), 2, '0')
+ '-'
+ PadLeft(ToString(DateTimeMonth([PERIOD])), 2, '0')
+ '-01'
This will extract what Alteryx thinks is the day, pad it with a leading zero, add the century to it, then do a similar operation for the month, and add those to the a hard coded day 1. Make sure to set your data type to date.
For your second requirement, you mixed up your format and example. In any case, a date field has to have a standard format. If you wanted a different format, it would have to be a string. For the sake of variety, here's a different tool, the DateTime tool.
I attached the workflow with just these few changes for you to explore. Please mark as resolved or resubmit requirements. Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Not sure why the original date is showing up as 2024/1/23. I'll check and let you know. Thank you for finding it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for the help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for your help
![](/skins/images/12A9B4B958288E867BE947DD48612FB8/responsive_peak/images/icon_anonymous_message.png)