I received some data from one of our European plants, I need to calculate the duration of certain events.
US format is mm-dd-yyyy hh:mm:ss, but the data from across the pond is dd-mm-yyyy hh:mm:ss.

When I try to calculate the duration in my US version of excel, I get whacky results.

Does anyone know of a way to convert the European date time format to US format?


thanks,

Ken

Hi Ken
If you right click on a cell and select format cells you can select date and change the format of the date to suit anywhere in the world.
Hope this helps.

Changing the format did not work, I believe it's because the date information is "text" instead of "date".

I did come up with a solution, may not be the best, but it works.

I use the text to column function and broke the European date in to four columns. Then used the concatenate function to put it back together in US style, then copied the values to another column and formatted as "date".

Only took about five minutes, once I got my brain around the problem.

The following macro will parse the cells any column for example B and place the revised data in column C. Took a little think time and test time but it worked in Excel 2007. Sorry I didn't add comments and document well.

Sub ParseCell()
Dim Source As String
Dim Target As String
Dim WinT, XinT, YinT, ZinT As Integer
Dim XroW, YcoL As Integer

XroW = ActiveCell.Row
YcoL = ActiveCell.Column

Do While Cells(XroW, YcoL).Value <> ""
XinT = 0
YinT = 0
Source = Cells(XroW, YcoL).Value
ZinT = Len(Source)
For WinT = 2 To ZinT
If Mid$(Source, WinT, 1) = "-" Then
If XinT = 0 Then
XinT = WinT
Else
YinT = WinT
End If
End If
Next WinT

Target = Mid$(Source, XinT + 1, YinT - XinT - 1) & "-"
Target = Target & Left$(Source, XinT - 1) & "-"
Target = Target & Mid$(Source, YinT + 1, ZinT - YinT)

Cells(XroW, YcoL + 1).Value = Target

XroW = XroW + 1

Loop

End Sub

MY SAMPLE DATA IS BELOW:
Euorpean Converted
02-06-2009 14:00:00 06-02-2009 14:00:00
01-06-2009 14:00:00 06-01-2009 14:00:00
02-06-2009 14:00:01 06-02-2009 14:00:01
01-06-2009 14:00:01 06-01-2009 14:00:01
02-06-2009 14:00:02 06-02-2009 14:00:02
01-06-2009 14:00:02 06-01-2009 14:00:02
02-06-2009 14:00:03 06-02-2009 14:00:03

Thanks Bob, must be on the night shift and fighting the boredom bug.
I will try the macro when I get to work. We are still using Office 2003, but shouldn't make a difference.

No Problem Ken. You might ahve to repalce the "-" with your delimiter if it is different.

Works like a charm.
thanks

Small potatoes for an old code horse like me. I enjoyed the challenge.