Date Format Cleanup

[KF1802]

Hi, 

I am attempting to convert a number of inconsistent date formats from an excel report input into a consistent dd/mm/yyyy date field.  

Any suggestions would be much appreciated.

[BrandonB]

You might be interested in the BB Date macro that was build to handle this type of problem: https://community.alteryx.com/t5/Engine-Works/Introducing-BB-Date-Date-Parsing-for-the-Rest-of-Us/ba... 

Download link: https://gallery.alteryx.com/#!app/BB-Date/577fe8aba248970840e6dd4d 

Otherwise you would need to leverage a formula with a conditional that looks for patterns and parses/converts accordingly. You would need an ElseIf for every format that would be coming in. 

[Emmanuel_G]

@KF1802 

One way of doing that in attachement.

[binuacs]

@KF1802 One way of doing this

[vlad_kutateladze]

@Emmanuel_G  Let me add that it is essential to subtract two days  in this formula because day 0 in Excel is actually 1899-12-30 and not 1900-01-01

So the correct formula will be: 

if !Contains([Input],"/") then 
DateTimeParse(DateTimeAdd("1900-01-01",tonumber([Input])-2,"days"),"%Y-%m-%d")
else DateTimeParse([Input],"%m/%d/%Y") endif

For example, 44691 is not 2022-05-12 but 2022-05-10  

[KF1802]

Thanks for the support.  Slight issue with the solution in its current form.  Where the report shows a numbered date such as 44722 Alteryx is showing in US format as 10/06/2022.  I would like the date to be converted into UK format and presented as 06/10/2022.  Is this possible?

[binuacs]

@KF1802 The correct date conversion of the number 44722 is 06/10/2022 (mm/dd/yyyy - US format) and when you convert it in to UK format it will be 10/06/2022 (dd/mm/yyyy format). I think the output is correct. I added different format for clarity

[KF1802]

@binuacs

"The correct conversion of the number 44722 is correct at 06/10/2022 (mm/dd/yyyy - US format) and when you convert it in to UK format it will be 10/06/2022 (dd/mm/yyyy format). I think the output is correct. I added different format for clarity". 

I agree with your statement above, however the formula is not delivering what I'm looking to achieve.

I'm using a 3rd party program from which as report is extracted to Excel and then into Alteryx.

+ 3rd Party Program:  Original date logged 05/10/2022 (UK) ie. 5th October 2022, or 10/05/2022 (US) or 44840

+ Extract to Excel: Date incorrectly converted from 44840 to 44722.  This is where the error occurs.  I'm trying to fix this date via Alteryx converting 44722 back to 44840, 5th October 2022.

I'm thinking if the formula can be amended to:

if cell contains a date in number format (44722) then convert date 10/06/2022 (UK) 10th June 2022, as per your existing code,

and then perform another action to convert the date from 10/06/2022 10th June 2022, to 06/10/2022 (UK) 5th October 2022 as it was originally entered. 

This would only apply to dates in number (44722) format as your formula is correctly displaying all other dates (i.e. anything with "/").

Hope that makes sense and thanks for your help.

[binuacs]

@KF1802 Attaching the updated workflow