Formatting date

Hello,

Not sure how many of you work with ?printing? reports to .csv and then opening the .csv files in Excel.

This is the situation.

We run WIP report for a period to csv, then we run a macro which deletes whatever columns are not needed, creates formulas to correct dates imported as text or as date, format number, insert subtotals, etc., leaving a clean report.
We do that on many reports.

Since there are thousands of lines, I never noticed that Excel incorrectly presents the date. Duh!

In the attached picture entries in columns for month, day and year are separated from the date dumped to csv using the Text to Columns function. Then the DATE formula uses those to create a date. Expected date, as in the picture is 2018 December 31.
It turns out that Excel produces number 44024 which is in fact Jul 12th 2020.

Did anyone encounter such behaviour and found a solution?

WrongDate.JPG

Hello MrShorty.

Yes, the formula is definitely wrong in this picture.
But it is not incorrect as it starts.

Please take a look at the two attached screenshots.WrongDate2.JPGWrongDate3.JPG

The .csv file changes the format. At the top when macro enters formula it is correct. Then copied down creates a wrong date.
Thank you for noticing. I wasn't even thinking about it.

Will change the formula to accommodate the length of entry in the original cell and choose the correct Y/M/D selection.

Thank you.