How to force a format from an odd Excel input?

[pierrelouisbescond]

Dear Alteryx fellows,

I am currently helping a colleague to consolidate a large number of Excel files coming from different world regions into a single one (it was taking hours manually… and now only a few seconds with Alteryx with the proper workflow :-) ).

The single issue I have is that one of these files is generated with the wrong Excel format for some columns. This means, for ex. that, instead of displaying “2018” it shows “10-Jul-05” in Excel ("dd-mmm-yy" used instead of the "standard" format).

Hereunder, columns #1 and #2 are in the wrong format; column #3 is fine:

I have no possibility of changing the Excel format so I was wondering if there was a way to force the format in Alteryx?

For some columns (#2 and #3), Alteryx does interpret properly the numbers but column #1 is still wrong as it should display “2018”:

And, quite strangely, after more than 300 lines, the wrong format is displayed everywhere:

To go back to normal for columns #2 and #3 around lines:

To back to normal for columns 2 and 3 around line 483:

I am attaching the Excel file for those who will be kind enough to have a look at it and the interpretation from Alteryx.

Thanks!
PLB

[mmenth]

Hi @pierrelouisbescond,

Are the dates wrong in the original files you are feeding into Alteryx or are they being converted to the incorrect dates somewhere in the Alteryx workflow?

Best,

mmenth

[estherb47]

Hi @pierrelouisbescond,

Would love to take a stab at this, but the Excel file is linking to data that can't be retrieved outside of your computer. Would you please break all links and just post with the data itself?

That said, I wonder if it's the linkeage that is causing the problems with format.

Best,

Esther

[pierrelouisbescond]

Hi EstherB47!

I have converted all links into values... and tested it again in Alteryx and the results are the same.

Hoping you can tell me what would be the proper way to fix this :-)

Pierre-Louis

[pierrelouisbescond]

Hi mmenth,

The values contained in the Excel cells are right but the format is wrong.

There is no specific worklflow in Alteryx, just the data entry.

Thanks,

PL

[estherb47 · Accepted solution]

Ah, the joys of Excel and serial dates.

@pierrelouisbescond, the first column needs to be turned back into the number it represents, which can be done with a DateDiff function (little bits of conversion in the formula tool to turn the text into a date, and then figure out the number of days between that date and 12/30/1899)

DateTimeDiff(DateTimeParse([Annee_Validation],"%Y-%m-%d"),"1899-12-30","days")

For the month column, created an IIF statement to see if the field was a date or just the number. If a date, a touch of manipulation to make it a real date, and then pull the month out.

ToNumber(IIF(REGEX_Match([Mois_Validation], "\d{4}-\d{2}-\d{2}"),  DatetimeFormat(DateTimeParse([Mois_Validation],"%Y-%m-%d"),"%m"), [Mois_Validation]))

Please let me know if anything needs further explanation.

Cheers!

Esther

[pierrelouisbescond]

@EstherB47

This works perfectly!

Thanks a lot for guiding me through the dark side of the Excel formats... and reg expressions ^^