Convert American Date to Australian Date format

[Noemi]

Hi
Does anyone know how to convert american dates into australian dates using
code or if not code then in excel.

My main reason for wanting this is to run a comparison between the 2 dates
to see if the match however if the american date has a lower number then the
program assumes it is an australian date.

Thanks
Noemi

 

[OssieMac]

Hi Noemi,

The following was a recent reply. I have left the contributors name on the
bottom to give credit where credit is due.

The following macro converts date strings and values in the selected range
between mm/dd/yyyy and dd/mm/yyyy. The output is in the
form of 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.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
With Application
On Error Resume Next ' In case there are no xlConstants or convertible
dates in DtRange
For Each oCell In DtRange.SpecialCells(xlConstants)
oTxt = oCell.Text
If UBound(Split(oTxt, "/")) = 2 Then _
oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/"
& Split(oTxt, "/")(2))
Next oCell
End With
End Sub

Note that no conversion is performed if the date is embedded in a formula or
the output would be an invalid date.

 

[Noemi]

Thanks OssieMac however I do not understand where oCell is getting it's
information from, it keeps showing cell A1, how do I change this.

Thanks
Noemi

Hi Noemi,

The following was a recent reply. I have left the contributors name on the
bottom to give credit where credit is due.

The following macro converts date strings and values in the selected range
between mm/dd/yyyy and dd/mm/yyyy. The output is in the
form of 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.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
With Application
On Error Resume Next ' In case there are no xlConstants or convertible
dates in DtRange
For Each oCell In DtRange.SpecialCells(xlConstants)
oTxt = oCell.Text
If UBound(Split(oTxt, "/")) = 2 Then _
oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/"
& Split(oTxt, "/")(2))
Next oCell
End With
End Sub

Note that no conversion is performed if the date is embedded in a formula or
the output would be an invalid date.

--
Cheers
macropod
[MVP - Microsoft Word]


--
Regards,

OssieMac

Hi
Does anyone know how to convert american dates into australian dates using
code or if not code then in excel.

My main reason for wanting this is to run a comparison between the 2 dates
to see if the match however if the american date has a lower number then the
program assumes it is an australian date.

Thanks
Noemi

 

[Noemi]

Thanks OssieMac

However I am not sure how oCell is getting its data from, it keeps refering
to cell A1 which is not what I want.

Can you help clarify.

Thanks
Noemi

Hi Noemi,

The following was a recent reply. I have left the contributors name on the
bottom to give credit where credit is due.

The following macro converts date strings and values in the selected range
between mm/dd/yyyy and dd/mm/yyyy. The output is in the
form of 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.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
With Application
On Error Resume Next ' In case there are no xlConstants or convertible
dates in DtRange
For Each oCell In DtRange.SpecialCells(xlConstants)
oTxt = oCell.Text
If UBound(Split(oTxt, "/")) = 2 Then _
oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/"
& Split(oTxt, "/")(2))
Next oCell
End With
End Sub

Note that no conversion is performed if the date is embedded in a formula or
the output would be an invalid date.

--
Cheers
macropod
[MVP - Microsoft Word]


--
Regards,

OssieMac

Hi
Does anyone know how to convert american dates into australian dates using
code or if not code then in excel.

My main reason for wanting this is to run a comparison between the 2 dates
to see if the match however if the american date has a lower number then the
program assumes it is an australian date.

Thanks
Noemi

 

[OssieMac]

Hi Noemi,

You have to select the range first. That is what the following code is
about. It either sets the range to a single selected cell or a selected range.

If Selection.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If


--
Regards,

OssieMac

Thanks OssieMac

However I am not sure how oCell is getting its data from, it keeps refering
to cell A1 which is not what I want.

Can you help clarify.

Thanks
Noemi

Hi Noemi,

The following was a recent reply. I have left the contributors name on the
bottom to give credit where credit is due.

The following macro converts date strings and values in the selected range
between mm/dd/yyyy and dd/mm/yyyy. The output is in the
form of 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.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
With Application
On Error Resume Next ' In case there are no xlConstants or convertible
dates in DtRange
For Each oCell In DtRange.SpecialCells(xlConstants)
oTxt = oCell.Text
If UBound(Split(oTxt, "/")) = 2 Then _
oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/"
& Split(oTxt, "/")(2))
Next oCell
End With
End Sub

Note that no conversion is performed if the date is embedded in a formula or
the output would be an invalid date.

--
Cheers
macropod
[MVP - Microsoft Word]


--
Regards,

OssieMac

Hi
Does anyone know how to convert american dates into australian dates using
code or if not code then in excel.

My main reason for wanting this is to run a comparison between the 2 dates
to see if the match however if the american date has a lower number then the
program assumes it is an australian date.

Thanks
Noemi