Convert European to US date format
Convert European to US date format
(OP)
Hello... TIA
I have a spreadsheet developed on a european (German) version of excel/windows.
The date columns are formatted for date "14-Mar-98", however they still are displayed as "14.03.03"
and idea on a way to convert this? I've found lots of date solutions... but not for this situation.
Thanks again!
I have a spreadsheet developed on a european (German) version of excel/windows.
The date columns are formatted for date "14-Mar-98", however they still are displayed as "14.03.03"
and idea on a way to convert this? I've found lots of date solutions... but not for this situation.
Thanks again!
Have a great day!
Scott





RE: Convert European to US date format
After trying many peoples solutions and much frustration... I just wrote somthing quick and dirty... Thought I'd put it up in case anyone else has similar trouble.
not pretty... but it does the job..
Sub ConvertDate()
Application.ScreenUpdating = False
Dim intCount As Integer
Dim strText As String
Dim intLength As Integer
Dim intFirst As Integer
Dim intSecond As Integer
Dim strNewDay As String
Dim strNewMonth As String
Dim strNewYear As String
Dim strMonthText As String
Dim strDate As String
Dim datDate As Date
Dim sngCellRow As Single
Dim sngCellCol As Single
sngCellRow = ActiveCell.Row
sngCellCol = ActiveCell.Column
Columns(ActiveCell.Column).Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Cells(sngCellRow, sngCellCol).Activate
Do Until ActiveCell.Rows = 65536
If ActiveCell.Value <> Empty Then
strText = ActiveCell.Value
intLength = Len(strText)
intFirst = InStr(ActiveCell.Value, ".")
intSecond = InStr(intFirst + 1, ActiveCell.Value, ".")
strNewDay = Left(strText, intFirst - 1)
strNewMonth = Mid(strText, intFirst + 1, intLength - (intSecond + 1))
strNewYear = Right(strText, intLength - intSecond)
Select Case strNewMonth
Case 1
strMonthText = "Jan"
Case 2
strMonthText = "Feb"
Case 3
strMonthText = "Mar"
Case 4
strMonthText = "Apr"
Case 5
strMonthText = "May"
Case 6
strMonthText = "Jun"
Case 7
strMonthText = "Jul"
Case 8
strMonthText = "Aug"
Case 9
strMonthText = "Sep"
Case 10
strMonthText = "Oct"
Case 11
strMonthText = "Nov"
Case 12
strMonthText = "Dec"
End Select
strDate = strNewDay & " " & strMonthText & " " & strNewYear
datDate = strDate
ActiveCell.Value = datDate
intCount = intCount + 1
End If
If ActiveCell.Row <> 65536 Then
Cells(ActiveCell.Row + 1, ActiveCell.Column).Activate
End If
If ActiveCell.Row = 65536 Then Exit Sub
Loop
Application.ScreenUpdating = True
End Sub
Have a great day!
Scott
RE: Convert European to US date format
One thing that has an effect is to change the system date configuration to something like dd.mm.yy or so, like they use in Germany. Excel then recognizes these fields as dates.
But I don't know what happens when you change the configuration back (after saving the spreadsheet). You might be back to square one, or it might be fine.
RE: Convert European to US date format
You could also consider a global replace of . by /
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Convert European to US date format
Right click | Format cells | custom
I can set a custom format as dd/mm/yyyy and enter dates in US format and they reset to Euro-format just fine.
I'm in US and using Office 2000.
Could the German version be that far behind? I don't think so!!
David
RE: Convert European to US date format
As you state correctly, once Excel has recognized a cell as a date, it can display it in any format, including the US and German formats.
RE: Convert European to US date format
=DATE(IF(VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,1)+1)))<78,VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,1)+1)))+2000,VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,1)+1)))+1900), MID(A1,FIND(".",A1,1)+1,FIND(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1), LEFT(A1,FIND(".",A1,1)-1))
The above assumes that A1 is the cell that has your date in it. Some sample outputs follow:
IN OUT
14.03.06 3/14/2006
22.3.77 3/22/2077
1.1.78 1/1/1978
1.1.1906 1/1/3806
RE: Convert European to US date format
Give that a try, and see if it works!
RE: Convert European to US date format
gerhardf (Electrical)
I didn't change the system date style or seperator because this would be a global change.
joerd (Chemical)
unfortunately the F2/enter had no effect
Flareman (Petroleum)
I did try the custom dates and it does work for entering new data, however coverting the format from dd/mm/yy to 14-Mar-03 did not work.
InHiding (Mechanical)
that's an impressive formula... I will make qood use of this! THANKS
melone (Electrical)
My situation is a global company and I must make sure the date is in a format local to the office or customer. The common separtor for european dates is a "." and imperial dates is the "/". All though I could change the separator, it would cause interpretation problems i.e. 11.4.03 vs 4.11.03
Thanks to everyone!!
Have a great day!
Scott
RE: Convert European to US date format
Change regional formats in the control panel.
Open the spreadsheet and the dates will now be displayed in the default format that you specify.
Excel is seeing the European format as a string and not as a date format. If it say it as a date format then it would convert this to a day serial number first and then display it as a date format.
Remember that Excel tracks dates as elapsed days since 1 Jan 1900. Excel simply displays this number as a common date format.
Rick Kitson MBA P.Eng
Construction Project Management
From conception to completion
www.kitsonengineering.com