Convert American Date to Australian Date format

N

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
 
O

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.
 
N

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

OssieMac said:
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


Noemi said:
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
Click to expand...
Click to expand...
 
N

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

OssieMac said:
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


Noemi said:
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
Click to expand...
Click to expand...
 
O

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


Noemi said:
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

OssieMac said:
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


Noemi said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VB converting UK to US dates, randomly, ignores cdate func! 3
Macros to convert Data into date format and then plot in graph 7
australian date format 1
Merged Date Field Converting from Australian layout to American 2
Date format converts from Australian to American when mail merging 4
Fixing the date format in a macro or VBA 1
American date conversion Macro 1
changing date formatted as general number into date format 4

Top