Convert European to US date format

[ByDesign]

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!

Have a great day!

Scott

 

[ByDesign]

Well boys...

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, &quot;.&quot
intSecond = InStr(intFirst + 1, ActiveCell.Value, &quot;.&quot

strNewDay = Left(strText, intFirst - 1)
strNewMonth = Mid(strText, intFirst + 1, intLength - (intSecond + 1))
strNewYear = Right(strText, intLength - intSecond)

Select Case strNewMonth
Case 1
strMonthText = &quot;Jan&quot;
Case 2
strMonthText = &quot;Feb&quot;
Case 3
strMonthText = &quot;Mar&quot;
Case 4
strMonthText = &quot;Apr&quot;
Case 5
strMonthText = &quot;May&quot;
Case 6
strMonthText = &quot;Jun&quot;
Case 7
strMonthText = &quot;Jul&quot;
Case 8
strMonthText = &quot;Aug&quot;
Case 9
strMonthText = &quot;Sep&quot;
Case 10
strMonthText = &quot;Oct&quot;
Case 11
strMonthText = &quot;Nov&quot;
Case 12
strMonthText = &quot;Dec&quot;
End Select

strDate = strNewDay & &quot; &quot; & strMonthText & &quot; &quot; & 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

 

[gerhardf]

Just an afterthought... It is likely that the fields didn't get recognized as dates because your system is set up to use US-style dates. I don't think there is a way around this within Excel (other than the textual conversion you wrote).

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.

 

[joerd]

Does it help if you edit the cell (press F2), and then press enter, to force XL to re-evaluate what was typed?
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.

 

[Flareman]

I'm confused by your problem.
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

 

[gerhardf]

Flareman, you say you use a system with US type dates. Try entering a date in the format of the OP, e.g. &quot;14.04.03&quot; (that's April 14, 2003). Your Excel won't recognize this as a date. The OP seems to have (had) a spreadsheet full with such cells that don't get recognized as dates.

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.

 

[ivymike]

here's a worksheet function that may work for you:
=DATE(IF(VALUE(RIGHT(A1,LEN(A1)-FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1,1)+1)))<78,VALUE(RIGHT(A1,LEN(A1)-FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1,1)+1)))+2000,VALUE(RIGHT(A1,LEN(A1)-FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1,1)+1)))+1900), MID(A1,FIND(&quot;.&quot;,A1,1)+1,FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1,1)+1)-FIND(&quot;.&quot;,A1,1)-1), LEFT(A1,FIND(&quot;.&quot;,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

 

[melone]

I use decimal points instead of /'s for all of my dates, and Excel doesn't have a problem with it. However, I had to change my Regional Settings in Windows (Start->Settings->Control Panel->Regional Options->Date->Date Seperator->&quot;.&quot. Also, you can change the date format in the same place.

Give that a try, and see if it works!

 

[ByDesign]

WOW,lots of replys... thanks for the interest and help!!!

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 &quot;.&quot; and imperial dates is the &quot;/&quot;. 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

 

[RDK]

The default number format is in Windows and applies to all Office documents.

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

http://www.kitsonengineering.com