Date conversion - Excel presents system report date incorrectly

Your system is holding the data in one way (YYYY MM DD) and displaying it another way (DD MM YY)

How are you copying the information from your system and how are you pasting into Excel?

Problem for system date format. You can do as per below.
> Go to Start
> Click on "Control Panel"
> Open "Regional and language options"
> In Regional options : Clcik on "Customize" > Click on "Date" tab & change as in "short date format" : dd/MMM/yyyy
and "long date format" : dddd, MMMM dd, yyyy
Clcik on "apply" & "ok"

Ah OK. Well, the system produces a report in .CSV format. Normally we just save the report as an .xlsx locally and go to work with the data, so no copying as such is done, I kind of "get what I get" from the system. There is no view in which I could copy the data straight from they delivering system unfortunately.

AVK, Yes, I was told to do so by others as well, however, this is exactly what I don't want to, as my other 58 reports which have for me the correct format on date, will then have the above described issue. To add to this, we generally can't change these settings on our laptops either - company policy. Thanks for replying though

Please attach the .csv file - I would like to see the original format before it comes into Excel and before anybody does anything to it. Perhaps you can run a short report without any confidential data - make sure the "dates" are ones that cause you the problem.

If that is not possible then attach the .xlsx file with "XXX" instead of text

thanks

I have attached a .csv file (report) as mentioned.

If you open the .csv file with Notepad these are the values

Company,SHP Date,LD Date
XYZ,2003-02-16,2005-05-16
XYZ,2003-02-16,2004-05-16
XYZ,2022-01-16,2025-04-16
XYZ,2022-02-16,2023-03-16
XYZ,2022-02-16,2002-06-16

So the problem is in how the data was exported to the .csv file

There are 3 choices:
1) BEST SOLUTION: fix the problem when data exported from system to .csv
2) ALTERNATIVE1 write VBA routine to convert the data
3) ALTERNATIVE2 use a formula to convert the data

Note
When the data is in Excel, Excel is changing the text into a number. All dates are held as numbers.
Excel sees 16/02/2003 as 37668
Excel sees 03/02/2016 as 42403


Are you able to ask somebody to fix the .csv file?
This problem is happening when the .csv file is being created.
I think someone has made an error that could be fixed

This solution assumes that all the "BAD" dates are between 01/01/2000 and 31/12/2999

Dates in Excel are simply numbers formatted a special way.
The "number" held by Excel is incorrect and so when formatted as a date is giving the incorrect date.
So we need to find a way to re-calculate the number.

Here is one way to convert:

"BAD" date is in B2

Formula:

Please Login or Register  to view this content.

For consistency I have treated "month" like "year" & "day" - but of course the month is correct in either format!

The formula
- formats what Excel treats as the "year" as 2 digit text (= DAY)
- formats the "month" as 2 digit text (= MONTH)
- formats what Excel treats as the "day" as 2 digit text (= YEAR with 2 digits)
- inserts "20" before those 2 digits (= YEAR with 4 digits)
- feeds those values into the Date function

Formula:

Please Login or Register  to view this content.

Excel also seems happy with

Please Login or Register  to view this content.

Late EDIT
And you can format in any date format you want, because the result is a number

If this is a regular problem you will need to keep putting adding the formula every time you receive a .csv (tedious??)

If you want a VBA solution to run against the .csv file I will need some more information
- which Columns need converting
- which row does the data start (not the Headings)
- are there any values other than dates in those columns?

It was very kind of you not to point out my exaggerated claim

This solution assumes that all the "BAD" dates are between 01/01/2000 and 31/12/2999

- I am sure you noticed that the solution is only good until 2099.
Glad your problem is resolved and that you have converted the formula to VBA.
Thanks for the reps

Below is VBA solution where values in Columns B,C,G & H are errant dates - starting in row2
The VBA overwrites the original values with the correct values.
The macro (as written) needs to be run from the sheet containing the values.

Please Login or Register  to view this content.