Excel date column read in alteryx

Question

I have a excel where 1 column is an date column. But few records are loaded like below.

Date44592

When I go to Number format and select short date in excel then the column is showing below date.

Date1/31/2022

Unfortunately, my data set is too large that I cannot format this column to short date for each row.

I have loaded the data in alteryx, and my column looks like below and its data type is vstring.

Date1/31/202244592

My question is --

1. How to convert that "44592" is respective date?

2. How to make that column read as a Date data type?

caltang · Answer

That's an awesome solution that covers many scenarios. Thanks for sharing @BS_THE_ANALYST - bookmarked!

BS_THE_ANALYST · Answer

@PB41091 I'll show a good approach to solve this type of problem:

Realistically, you can solve this all in one formula tool with a big IF statement. However, it can become messy and difficult for others to inherit. This approaches just adopts the idea of filtering your different patterns of dates separately and parsing them into actual dates according to their pattern, then union them back on top of eachother afterwards.

If you want an example of how to solve this type of problem, all within one formula tool, here's the link to the Weekly Challenge: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-4-Date-Parsing/td-p/36731 and I'd advise checking out @MarqueeCrew's solution (you can find this on the second page of the solution on that weekly challenge):

All the best,

BS

datesWorkflow.yxmd