How to force a format from an odd Excel input?

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:

screenshot.2019-01-22 (6).png

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”:

screenshot.2019-01-22 (4).png

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

screenshot.2019-01-22 (7).png

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

screenshot.2019-01-22 (8).png

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

screenshot.2019-01-22 (8).png

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

Alteryx_Excel_Input_Data_Issue.xlsx

Input

Date Time

Accepted answers

estherb47

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

DateFormatProblems.yxmd

All comments

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

Alteryx_Excel_Input_Data_Issue.xlsx

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,

estherb47

Ah, the joys of Excel and serial dates.

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

DateFormatProblems.yxmd

pierrelouisbescond

@EstherB47

This works perfectly!

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703