Alteryx Designer Desktop Discussions

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

How to create correct Date Field

srk0609
8 - Asteroid

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.

5 REPLIES 5
seven
12 - Quasar

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.

community_2024_07_06.png

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.

community_2024_07_06_2.png

 

I attached the workflow with just these few changes for you to explore. Please mark as resolved or resubmit requirements. Thank you.

 

Qiu
21 - Polaris
21 - Polaris

@srk0609 

In addition to the reply from @seven I added the "LastofMonth" syntax with DateTimeTrim function.

But I do observe that the original date in your data in year of 2024.

 

0707-srk0609.png0707-srk0609-A.png

srk0609
8 - Asteroid

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.

srk0609
8 - Asteroid

Thank you for the help

srk0609
8 - Asteroid

Thank you for your help

Labels
Top Solution Authors