Excel issue with dates being changed

Question

&nbsp;We have 7 Excel reports (.xlsx) that are downloaded from various different sources and put onto a shared drive, they are then picked up by a BOT, which uses Alteryx to combine them into 1 large report (.csv) that it places on the shared drive.&nbsp; The issue is that sometimes on the 1 large report, the Start Date and End Date columns are fine for most rows, but some rows have entries which are no longer dates, and don't match any dates in the individual 7 reports for that row.&nbsp;For example&nbsp;the report which pulls in the dates into the 1 large report would have;&nbsp;01-Jan-201830-Jun-2018&nbsp;&nbsp;but when the dates are pulled into the 1 large report, instead of appearing as;&nbsp;01/01/201830/06/2018&nbsp;they appear as;&nbsp;02/26/414918501/31/4191765&nbsp;&nbsp;The really strange thing is this issue only appears for certain people.&nbsp; Other people can follow exactly the same steps, download the 7 reports from the same sources, and when the 1 large report is created, all dates are as expected, with no problems.&nbsp; And some of these people who could run these reports without problems for months (while others had this problem), one day they start getting this problem.&nbsp; And once someone gets this problem it doesn't go away, so it's reducing the pool of people who can do this work.&nbsp;So I don't think it has anything to do with Alteryx or the data transformation, I'm assuming it's something user specific but if I compare the downloaded reports for a person who has the issue, and another who doesn't I can't see any format differences, there aren't any differences with version of Excel or Office between these people, I can't see a reason.&nbsp; The randomness of it doesn't make sense.&nbsp;

sergeibaklan · Answer

JohnMcAteer&nbsp;
I don't know what that could be, but that's not Excel. Look, you have few reports where dates are correct. Some other tool transforms and combines these reports into csv file. The latest is just text file, you could open it in Notepad or any other editor and check if dates are correct or not. And/or separators in csv are added correctly or not.
&nbsp;

johnmcateer · Answer

SergeiBaklan&nbsp;Thanks for the response. If I open in Notepad the dates appear exactly as in CSV, and the separators look OK.&nbsp; I know CSV is just a text file, but I don't understand why the user who generates it makes a difference.&nbsp; To me that would indicate the transform &amp; combine isn't relevant as otherwise it would happen to every user.&nbsp; If I look at the individual reports (xlsx) of a user who has this issue, and another who doesn't I cannot see any differences, but it would appear this is the cause of the differing outputs.&nbsp;

sergeibaklan · Answer

JohnMcAteer&nbsp;
"transform &amp; combine" - is that Power Query or some other tool?

johnmcateer · Answer

SergeiBaklan&nbsp;I was originally told this was Alteryx doing this, but was misinformed.&nbsp;&nbsp;They are using an R script to combine the spreadsheets into a single CSV file.&nbsp; I would normally think the issue lay here, but that doesn't explain why the issue presents for some users but not others, when they are following exactly the same process, and the same R script is doing the work.&nbsp; Also, for problem users, why are only some rows affected and not all in the CSV.

joeuser2004 · Answer

JohnMcAteer&nbsp; wrote:&nbsp; ``I was originally told this was Alteryx doing this, but was misinformed. They are using an R script to combine the spreadsheets into a single CSV file``&nbsp;Whether it is Alteryx, R script, or something else, the point is:&nbsp; if you see the bogus "dates" in the CSV file itself (e.g. using Notepad), then it is the creator of the CSV that is at fault.&nbsp;It sounds like Excel is not used to create the CSV file.&nbsp; Right?&nbsp;If so, then it is not an Excel problem.&nbsp; Go to a forum (or customer support) for the product that you believe creates the CSV file.

Forum Discussion

Excel issue with dates being changed

Share

Resources