format dates (2000)

[tjuni]

I have a worksheet containing a column of dates. Some are in the format 102103 - i.e. month, day, year without any separation between them, and some are 10/21/2003 - i.e. separated by forward slashes. I want to format all of the cells in this column to contain the forward slashes as in the second example. Is there any simple way for me to do this? If not, and I have to write some VBA code, how do I loop through the cells of a range that has been selected by the user and format each one?

[JohnBF]

It would help to know the underlying cell formats but this quick and dirty macro should work:

Sub datefix()
Dim rngCell As Range
For Each rngCell In Selection ' this is one way to loop through a user selection
If CBool(InStr(rngCell.Value, "/")) Then
rngCell.Value = rngCell.Value ' in case cell format is text
Else
rngCell.Value = DateValue(Left(rngCell.Value, 2) & "/" & _
Mid(rngCell.Value, 2, 3) & "/" & Right(rngCell.Value, 2))
End If
Next rngCell
Selection.NumberFormat = "mm/dd/yy"
End Sub

Please test it for all conditions you are encountering. This could also be done with formulas such as =FIND() and =DATEVALUE().

[macropod]

Hi John,

I think you'd need to change the Else processing to something like:
rngCell.Value = DateValue(Left(rngCell.Value, Len(rngCell.Value) - 4) & "/" & _
Mid(rngCell.Value, 2, Len(rngCell.Value) - 3) & "/" & Right(rngCell.Value, 2))
so as to cater for Excel's habit of dropping leading 0s.

Cheers

[tjuni]

Thanks guys. I appreciate your help. I got the task done by creating a macro similar to John's. I guess there isn't any built-in Excel function that would have done the work for me, huh?

[HansV]

If all dates had been in one format, you might have used Data | Text to Columns..., but the mixture of formats makes that impracticable.

[tjuni]

Thank you, Hans!! Despite the fact that the column contained dates formatted in 2 different ways (i.e. a) 100103 b)10/1/03), the Text-to-Columns feature worked to convert the entire range to dates. Kudos.

[macropod]

Hi Taibe,

Depending on how your data is produced, you may need to be able to work with any or all of the following:
01/02/03
1/02/03
01/02/2003
1/02/2003
010203
10203
01022003
1022003
In any event, Text-To-Columns would still give the day, month & year in three different columns.

The following formula in, say, B1:
=DATEVALUE(LEFT(SUBSTITUTE(A1,"/",""),LEN(SUBSTITUTE(A1,"/",""))-4-(LEN(SUBSTITUTE(A1,"/",""))>6)*2)&"/"&MID(SUBSTITUTE(A1,"/",""),LEN(SUBSTITUTE(A1,"/",""))-3-(LEN(SUBSTITUTE(A1,"/",""))>6)*2,2)&"/"&RIGHT(SUBSTITUTE(A1,"/",""),2))
will convert the string in A1, which can be in any of the above formats, into a 'proper' Excel datevalue, which you can then format as a date using cell formatting. This gives your whole date in result in a single column instead of spreading it over three.

Cheers

[LegareColeman]

Be aware that Text to columns will not properly convert 100103 to the date 10/1/03. It will convert it to 1/25/2174 (displayed as 1/25/74 if you format to two digit years).