UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> European Date Conversion    
European Date Conversion

Translating European Date Imports into U.S. Format

Chances are if you receive a spreadsheet from Europe with dates, the only thing you should not have an issue with compatibility. The dates are stored as numbers. It’s only the display that’s an issue and if your settings are to display dates in U.S. format, then they will be displayed properly.

The problem happens when data is imported from another standard. U.S. format for dates is mm/dd/yyyy while European format is dd/mm/yyyy. The day and month positions are interchanged.

This causes issues on import as Excel attempts to interpret the data. Some values will look like valid dates such as 3/12/2015. This is December 3, 2015 in the European standard but Excel thinks it’s March 12, 2015 in the U.S. standard.

Other European-style dates such as 30/3/2015 (March 30th, 2015) won’t make sense as a U.S.-Style date and will be imported as strings.

These two different interpretations need to be handled in different ways.

If Excel is able to interpret the date as a date, then it stores it as a number and this can be used to determine which transformation method to use.

This wiki provides both a formula-based solution and a VBA module solution to the transformation.

Formula Based Method

If the imported value can be interpreted as a date, the formula is relatively simple: disassemble the date into Month, Day and Year and re-assemble it in the other format.

So if the date is in Cell A2,
Year = YEAR(A2)
Month = MONTH(A2)
DAY = DAY(A2)

Since the European format puts the day in front of the month, these need to be put back in reverse order.

=DATE(YEAR(A2),DAY(A2),MONTH(A2))

Reformatting a date imported as a string is more problematic. The pieces have to be parsed out using Find, Mid, Left and Right. The attached spreadsheet Columns C:K shows how this is done using helper columns. If you take the formula in Column K and substitute the formulas in the other helper columns until defining the formula only in terms of Column A.

This process results in the following formula:

DATE(MID(A2,FIND("/",A2,FIND("/",A2,1)+1)+1,FIND(" ",A2)-FIND("/",A2,FIND("/",A2,1)+1)-1),MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1),LEFT(A2,FIND("/",A2,1)-1))+TIMEVALUE(RIGHT(A2,LEN(A2)-FIND(" ",A2)))

The formula for numeric and string are combined into one formula using an if statement:

=IF(ISNUMBER(A2)=TRUE,DATE(YEAR(A2),DAY(A2),MONTH(A2))+TIME(HOUR(A2),MINUTE(A2),SECOND(A2)),DATE(MID(A2,FIND("/",A2,FIND("/",A2,1)+1)+1,FIND(" ",A2)-FIND("/",A2,FIND("/",A2,1)+1)-1),MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1),LEFT(A2,FIND("/",A2,1)-1))+TIMEVALUE(RIGHT(A2,LEN(A2)-FIND(" ",A2))))

Sometimes the European date style uses periods (.) to separate the date parts instead of slashes. Change “/” to “.” if you encounter this.

VBA Solution

Syntax: To_US_Date (DateRef,[Delimiter])
DateRef is the date to be converted. It may be numeric or string data.
(Optional)Delimiter is the delimiter that separates the date parts, the default is "/".

CODE

Function To_US_Date(DateTime As Variant, Optional Delimiter As String = "/") As Date
Dim MyYear As Variant
Dim MyMonth As Variant
Dim MyDay As Variant
Dim MyTime As Variant
Dim MyHour As Variant
Dim MyMinute As Variant
Dim MySecond As Variant
Dim RtnDate As Date
Dim Find1 As Integer
Dim Find2 As Integer
Dim Find3 As Integer
Dim MyLen As Integer


If Application.WorksheetFunction.IsNumber(DateTime) = True Then
   MyYear = Year(DateTime)
   MyMonth = Day(DateTime)
   MyDay = Month(DateTime)
   MyHour = Hour(DateTime)
   MyMinute = Minute(DateTime)
   MySecond = Second(DateTime)
   RtnDate = DateSerial(MyYear, MyMonth, MyDay) + TimeSerial(MyHour, MyMinute, MySecond)
Else
   Find1 = InStr(1, DateTime, Delimiter)
   Find2 = InStr(Find1 + 1, DateTime, Delimiter)
   Find3 = InStr(Find2 + 1, DateTime, " ")
   MyLen = Len(DateTime)
   MyDay = Left(DateTime, Find1 - 1)
   MyMonth = Mid(DateTime, Find1 + 1, Find2 - Find1 - 1)
   MyYear = Mid(DateTime, Find2 + 1, Find3 - Find2 - 1)
   MyTime = Right(DateTime, MyLen - Find3)
   
   RtnDate = DateSerial(MyYear, MyMonth, MyDay) + TimeValue(MyTime)
End If

To_US_Date = RtnDate

End Function

Media:European Date.zip

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 1,418 times.  This page was last modified 14:50, 1 March 2015 by dflak.   Disclaimers