Conversion of European Dates to US dates

**ttyler916** · 02-16-2013, 06:23 PM

There are a couple of threads on this subject already but the base assumptions of the responders are incorrect.
The dates were never converted from text or played with, they are simply entered in European used spreadsheets set to accept European dates, often in dmy format, which allows each element to be of variable length.
Like many other Excel international users, we receive European invoices bearing dates in European date formats.
When opened in US based versions and sorting by date, the sorts are incorrect, assuming somehow that there are up to 31 months in a year and only 12 days in a month.
Performing a date conversion to the standard 1900 date sequence and then converting back to a dd-mm-yyyy format should allow correct sorting, but excel seems to have problems recognizing that the dates supplied are really European dates.
So some help is solicited.

**Richard Buttrey** · 02-16-2013, 06:53 PM

Hi,

As long as the dates are proper date numbers it matters not what the locale setting was when they were created. The underlying cell is still a number and will be interpreted according to the your regional setting. i.e. if set to say UK standard date numbers will appear as dd/mm/yyyy if a US setting then mm/dd/yyyy.

It would appear that the 'dates' when captured are actually text. The simplest solution, and stating the obvious, is to ensure that dates are captured as a number. If this isn't possible then you'll need to convert text to a number and format it accordingly. To do this you'll need to know the regional setting when the date was produced - perhaps as an additional flag passed along with the data from the original system. Knowing that you can use that as a test in a normal Excel string slicing function, or alternatively create a UDF to handle the conversion.

You also seem to be implying that the dates may in addition have been created on a Mac where the base for dates is 1904. So again you will need to know whether a Mac or Windows based version of Excel was used in order to determine whether in addition you need to adjust by 4 years.

**ttyler916** · 02-16-2013, 07:04 PM

When customers send us bookings in an excel spreadsheet, just how are we to know what machine/regional setting that customer user was using.
Even if the booking is coming from Holland, it could have been entered by company in Indonesia or the Philippines, or wherever data entry labor costs are inexpensive.
So I am afraid your suggestion is not very helpful in a business environment.
Somehow excel needs to record what the regional settings and machine base date is being used to ensure compatibility across international boundaries. At this point it is not internationally compatible.
Perhaps the solution is to open the file in csv format. I will try and let you know how that works.

**Richard Buttrey** · 02-16-2013, 08:29 PM

Hi,

I'm sorry you found my suggestion not very helpful.
But the main point I was trying to put across is that if whoever captures the data should capture a proper date number i.e. the date serial number. It won't then matter one jot where or who views the workbook with whatever regional setting they happen to be using at the time. Or in other words whatever the regional setting may be at the time of capture, Chinese, US, European, etc. the date serial number will always be the same. The 17th February will always be number 41322 (unless it's captured on a Mac. which is another problem to solve in a similar way by capturing the Application.OperatingSystem variable.)

But in the absence of being able to do this, that's why I suggested you try and also capture the regional setting being used at the time so that if the 'date' is captured as text you can use the knowledge about the setting to string slice the text date and from the string slicing create a proper date serial number.

For instance in my UK regional setting the instruction Application.International(xlCountrySetting) returns the value 44, and knowing this if someone in the US sees a text date 12/3/2013, and they also know this has been created on a system that had the international value of 44, they can be sure that it means 12 March 2013 and not 3rd December 2013

In answer to your first question you can only know the setting if you take steps to make sure that whenever the data is captured, the regional setting is recorded in a spare cell somewhere. Otherwise you can't rely on Excel to interpret things correctly. There's no getting away from this so in your business environment you'll need to ensure this is the case.

I hope that explains a little better and is helpful.

**ttyler916** · 02-18-2013, 04:50 AM

I found that this specific CSV file being sent was created on a UNIX box so the dates are manually keyed into the Unix database (or collected from elsewhere) in a standard date format such as d?/m?/yyyy.
When opened in excel in a NAM region set up those dates with d/m/yyyy where d<=12 will generated a serial date value based upon d?/m? where d is treated as the month and m as the day, based upon 1900.
If were to look at the first character by selecting Left(cell,1) you will immediately recover the number 4, being the first digit of the five digit date value.
Copying the date into a text field is no help as it faithfully copies the five digit date value and not the formatted date.
However copying the columns, [I have roughly 5-10,000 date values per column] from the spreadsheet, into predefined text columns of an opened csv file set in the format mmm dd appears
to eliminate the problem and provides me with a text string of the form "Mar 8" which is easy to change into 3/8/2012. For those entries with the date of the form dd/mm/yyyy it is again
easy to change them into mm/dd/yyyy.

The difficulty is, the selection of columns and their saving and then copying and saving back into our booking files still has to be performed manually, while the remaining 99 steps of our process
can be automated. I would still appreciate some advice on how to automate this process. The files are currently e-mailed and we have an automatic process to extract the attachments and work on them.
I suspect I will need to use a VB program to obtain what I need.

**ttyler916** · 02-18-2013, 04:58 AM

I found that this specific CSV file being sent was created on a UNIX box so the dates are manually keyed into the Unix database (or collected from elsewhere) in a standard date format such as d?/m?/yyyy.
When opened in excel in a NAM region set up those dates with d/m/yyyy where d<=12 will generated a serial date value based upon d?/m? where d is treated as the month and m as the day, based upon 1900.
If were to look at the first character by selecting Left(cell,1) you will immediately recover the number 4, being the first digit of the five digit date value.
Copying the date into a text field is no help as it faithfully copies the five digit date value and not the formatted date.
However copying the columns, [I have roughly 5-10,000 date values per column] from the spreadsheet, into predefined text columns of an opened csv file set in the format mmm dd appears
to eliminate the problem and provides me with a text string of the form "Mar 8" which is easy to change into 3/8/2012. For those entries with the date of the form dd/mm/yyyy it is again
easy to change them into mm/dd/yyyy.

I now realize that that my difficulty is attempting to see the file in Excel.
I just need to process the file with opening in Excel and then the date serial value will never appear.
Unfortunately, it still means, as you said, that the origin of teh file must be carried in teh body of teh file and actions than taken based upon that origin.