How to create correct Date Field

srk0609

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.

5Rows83100.xlsx

83100.yxmd

seven

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.

83100.yxmd