×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Convert European to US date format

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!

Have a great day!

Scott

RE: Convert European to US date format

(OP)
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, ".")
    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

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.

RE: Convert European to US date format

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.

RE: Convert European to US date format

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

RE: Convert European to US date format

Flareman, you say you use a system with US type dates. Try entering a date in the format of the OP, e.g. "14.04.03" (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.

RE: Convert European to US date format

here's a worksheet function that may work for you:
=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

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->".").  Also, you can change the date format in the same place.

Give that a try, and see if it works!

RE: Convert European to US date format

(OP)
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 "." 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

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
www.kitsonengineering.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources