American date conversion macro

[Guest]

Does anyone have some simple code that I can use to convert American date
format to English format. eg. 12/25/06 to 25/12 06. It will have to take into
account single and double digits.

Cheers,
Ant.

 

[macropod]

Hi Ant,

If the data are in a text file you want to import, changing your system's
regional settings to the US date format (eg mm-dd-yyyy) before importing the
data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

 

[Guest]

Thanks. That kind of works, however when I used the example 12/25/06 it
returned 02/12/06 not 25/12/06. Also do you know the VBA code I could apply a
macro button to in order to accomplish the same conversion result without
having to add your formula each time. Cheers.

Hi Ant,

If the data are in a text file you want to import, changing your system's
regional settings to the US date format (eg mm-dd-yyyy) before importing the
data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

--
macropod
[MVP - Microsoft Word]

Does anyone have some simple code that I can use to convert American date
format to English format. eg. 12/25/06 to 25/12 06. It will have to take into
account single and double digits.

Cheers,
Ant.

 

[macropod]

Hi Ant,

The formula gets a bit (lot) munged by the NG formatting.

The end of the 1st line and start of the 2nd line should come out as:
" "
The end of the 2nd line and start of the 3rd line should come out as:
"/"
That combo correctly turns 12/25/06 into 25/12/06.

For a quick & dirty macro version of this, which will change any dates in
the selected range , try:
Sub ConvertDateFormat2()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = oCell.Text
oCell.Value = "'" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) +
1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Left(oTxt, .WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

You'll notice the expression 'oCell.Value = "'" & Mid('. This retains the
resulting date as a string. If you want the result to be a date value
instead, change this to 'oCell.Value = Mid('. Be careful, though, as making
a mistake with this and applying it to a non-US date string may mean a lot
more work getting the date back.

Cheers

--
macropod
[MVP - Microsoft Word]

Thanks. That kind of works, however when I used the example 12/25/06 it
returned 02/12/06 not 25/12/06. Also do you know the VBA code I could apply a
macro button to in order to accomplish the same conversion result without
having to add your formula each time. Cheers.

Hi Ant,

If the data are in a text file you want to import, changing your system's
regional settings to the US date format (eg mm-dd-yyyy) before importing the
data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

--
macropod
[MVP - Microsoft Word]

Does anyone have some simple code that I can use to convert American date
format to English format. eg. 12/25/06 to 25/12 06. It will have to

take
into

account single and double digits.

Cheers,
Ant.

 

[Guest]

That's perfect. I made the change you mentioned and it works both ways. Nice
one.

Hi Ant,

The formula gets a bit (lot) munged by the NG formatting.

The end of the 1st line and start of the 2nd line should come out as:
" "
The end of the 2nd line and start of the 3rd line should come out as:
"/"
That combo correctly turns 12/25/06 into 25/12/06.

For a quick & dirty macro version of this, which will change any dates in
the selected range , try:
Sub ConvertDateFormat2()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = oCell.Text
oCell.Value = "'" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) +
1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Left(oTxt, .WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

You'll notice the expression 'oCell.Value = "'" & Mid('. This retains the
resulting date as a string. If you want the result to be a date value
instead, change this to 'oCell.Value = Mid('. Be careful, though, as making
a mistake with this and applying it to a non-US date string may mean a lot
more work getting the date back.

Cheers

--
macropod
[MVP - Microsoft Word]

Thanks. That kind of works, however when I used the example 12/25/06 it
returned 02/12/06 not 25/12/06. Also do you know the VBA code I could apply a
macro button to in order to accomplish the same conversion result without
having to add your formula each time. Cheers.

Hi Ant,

If the data are in a text file you want to import, changing your system's
regional settings to the US date format (eg mm-dd-yyyy) before importing the
data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

--
macropod
[MVP - Microsoft Word]


Does anyone have some simple code that I can use to convert American date
format to English format. eg. 12/25/06 to 25/12 06. It will have to take
into
account single and double digits.

Cheers,
Ant.

 

[Guest]

....actually... It works one some dates but not others. For example the
12/25/06 date converts to 25/12/06, however if it is a date that could read
either way ie with numbers less than 12 then it doesn't convert, for example
if you had 01/10/06 (Jan 110th 2006 in US format) it does not convert to 10th
Jan 2006. I guess the code needs to swap the month and day regardless.

Hi Ant,

The formula gets a bit (lot) munged by the NG formatting.

The end of the 1st line and start of the 2nd line should come out as:
" "
The end of the 2nd line and start of the 3rd line should come out as:
"/"
That combo correctly turns 12/25/06 into 25/12/06.

For a quick & dirty macro version of this, which will change any dates in
the selected range , try:
Sub ConvertDateFormat2()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = oCell.Text
oCell.Value = "'" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) +
1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Left(oTxt, .WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

You'll notice the expression 'oCell.Value = "'" & Mid('. This retains the
resulting date as a string. If you want the result to be a date value
instead, change this to 'oCell.Value = Mid('. Be careful, though, as making
a mistake with this and applying it to a non-US date string may mean a lot
more work getting the date back.

Cheers

--
macropod
[MVP - Microsoft Word]

Thanks. That kind of works, however when I used the example 12/25/06 it
returned 02/12/06 not 25/12/06. Also do you know the VBA code I could apply a
macro button to in order to accomplish the same conversion result without
having to add your formula each time. Cheers.

Hi Ant,

If the data are in a text file you want to import, changing your system's
regional settings to the US date format (eg mm-dd-yyyy) before importing the
data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"
"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

--
macropod
[MVP - Microsoft Word]


Does anyone have some simple code that I can use to convert American date
format to English format. eg. 12/25/06 to 25/12 06. It will have to take
into
account single and double digits.

Cheers,
Ant.

 

[macropod]

Hi Ant,

...actually... It works one some dates but not others.

That's probably because the dates it's working with have been interpreted by
Excel as text strings, whereas the dates it's not working with have already
been interpreted by Excel as date values.

Try the updated code below. It'll convert both forms from mm/dd/yyyy
strings/values to dd/mm/yyyy date values that you can format as dates via
Format|Cells|Number|Date.

Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = "'" & oCell.Text
oCell.Value = Mid(oTxt, 2, .WorksheetFunction.Find("/", oTxt) - 2) _
& "/" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) + 1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

Cheers


--
macropod
[MVP - Microsoft Word]

...actually... It works one some dates but not others. For example the
12/25/06 date converts to 25/12/06, however if it is a date that could read
either way ie with numbers less than 12 then it doesn't convert, for example
if you had 01/10/06 (Jan 110th 2006 in US format) it does not convert to 10th
Jan 2006. I guess the code needs to swap the month and day regardless.

Hi Ant,

The formula gets a bit (lot) munged by the NG formatting.

The end of the 1st line and start of the 2nd line should come out as:
" "
The end of the 2nd line and start of the 3rd line should come out as:
"/"
That combo correctly turns 12/25/06 into 25/12/06.

For a quick & dirty macro version of this, which will change any dates in
the selected range , try:
Sub ConvertDateFormat2()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = oCell.Text
oCell.Value = "'" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) +
1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Left(oTxt, .WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

You'll notice the expression 'oCell.Value = "'" & Mid('. This retains the
resulting date as a string. If you want the result to be a date value
instead, change this to 'oCell.Value = Mid('. Be careful, though, as making
a mistake with this and applying it to a non-US date string may mean a lot
more work getting the date back.

Cheers

--
macropod
[MVP - Microsoft Word]

Thanks. That kind of works, however when I used the example 12/25/06 it
returned 02/12/06 not 25/12/06. Also do you know the VBA code I could apply a
macro button to in order to accomplish the same conversion result without
having to add your formula each time. Cheers.

:

Hi Ant,

If the data are in a text file you want to import, changing your system's
regional settings to the US date format (eg mm-dd-yyyy) before

importing
the

data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"

"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/

",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"

"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/

",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

--
macropod
[MVP - Microsoft Word]


Does anyone have some simple code that I can use to convert

American
date

format to English format. eg. 12/25/06 to 25/12 06. It will have

to
take

into
account single and double digits.

Cheers,
Ant.

 

[Simon King]

Just wanted to say this is the business.

I needed to import event logs to create Pivot tables on the errors &
warnings.

The date was all messed up and this worked a treat, so much so that this
comment may help others when searching.
--
Currently working for Provoke Solutions (www.provoke.co.nz)

Hi Ant,

...actually... It works one some dates but not others.

That's probably because the dates it's working with have been interpreted by
Excel as text strings, whereas the dates it's not working with have already
been interpreted by Excel as date values.

Try the updated code below. It'll convert both forms from mm/dd/yyyy
strings/values to dd/mm/yyyy date values that you can format as dates via
Format|Cells|Number|Date.

Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = "'" & oCell.Text
oCell.Value = Mid(oTxt, 2, .WorksheetFunction.Find("/", oTxt) - 2) _
& "/" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) + 1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

Cheers


--
macropod
[MVP - Microsoft Word]

...actually... It works one some dates but not others. For example the
12/25/06 date converts to 25/12/06, however if it is a date that could read
either way ie with numbers less than 12 then it doesn't convert, for example
if you had 01/10/06 (Jan 110th 2006 in US format) it does not convert to 10th
Jan 2006. I guess the code needs to swap the month and day regardless.

Hi Ant,

The formula gets a bit (lot) munged by the NG formatting.

The end of the 1st line and start of the 2nd line should come out as:
" "
The end of the 2nd line and start of the 3rd line should come out as:
"/"
That combo correctly turns 12/25/06 into 25/12/06.

For a quick & dirty macro version of this, which will change any dates in
the selected range , try:
Sub ConvertDateFormat2()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = oCell.Text
oCell.Value = "'" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) +
1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Left(oTxt, .WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

You'll notice the expression 'oCell.Value = "'" & Mid('. This retains the
resulting date as a string. If you want the result to be a date value
instead, change this to 'oCell.Value = Mid('. Be careful, though, as making
a mistake with this and applying it to a non-US date string may mean a lot
more work getting the date back.

Cheers

--
macropod
[MVP - Microsoft Word]


Thanks. That kind of works, however when I used the example 12/25/06 it
returned 02/12/06 not 25/12/06. Also do you know the VBA code I could
apply a
macro button to in order to accomplish the same conversion result without
having to add your formula each time. Cheers.

:

Hi Ant,

If the data are in a text file you want to import, changing your
system's
regional settings to the US date format (eg mm-dd-yyyy) before importing
the
data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date
format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"

"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"

"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

--
macropod
[MVP - Microsoft Word]


Does anyone have some simple code that I can use to convert American
date
format to English format. eg. 12/25/06 to 25/12 06. It will have to
take
into
account single and double digits.

Cheers,
Ant.