Dates - help for us Aussies!

[LinLin]

I have a list of dates to import into excel which, if the month is less than
13, becomes the day, and vice versa.

So, if my original date was 1st October 2008, the export result in excel is
10/01/08.
If my original date was 1st April 1999, the result is 4/01/99.

Rather than quibble about the program that does this to my data, does anyone
have a formula/solution I could apply that I could apply to a list of dates
and it would "flip around" so the month becomes month again, day becomes day.
Note: List also has normal dates with months >12, those dates are correct
because the export recognises that there are no months beyond 12.

(Apologies if this has come up before, or if you can direct me to a previous
solution, that would be fine!)

 

[OssieMac]

Hi LinLin,

What type of file are you importing from and are the dates in the correct
format in the original file?

I have previously had a problem importing dates from csv files because when
importing the csv files the Text to Columns dialog box is bypassed and one
does not get the opportunity to select the date format.

My workaround was to turn on file extensions in windows explorer and rename
the file to a txt file. (Ignore the warning message about renaming the
extension.) Then when the txt file is imported, the Text To Columns dialog
box is displayed and by working through its screens one at a time you will
come to one where you can select a column and then select the date format for
that column.

 

[macropod]

Hi LinLin,

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.

 

[LinLin]

That did the trick nicely - excellent little macro.

One day I hope to have a brain like yours!
(My IF Statements are improving at least!)

cheerio
LinLin

Hi LinLin,

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]

I have a list of dates to import into excel which, if the month is less than
13, becomes the day, and vice versa.

So, if my original date was 1st October 2008, the export result in excel is
10/01/08.
If my original date was 1st April 1999, the result is 4/01/99.

Rather than quibble about the program that does this to my data, does anyone
have a formula/solution I could apply that I could apply to a list of dates
and it would "flip around" so the month becomes month again, day becomes day.
Note: List also has normal dates with months >12, those dates are correct
because the export recognises that there are no months beyond 12.

(Apologies if this has come up before, or if you can direct me to a previous
solution, that would be fine!)