Hi, I'm writing a Macro, and one of the date columns I want to sort by has
the date in 20060221 format (i.e. 21-Feb-2006), how can I get it a format
that Excel will recognize as a date.

Thanks,
Hussein

Format cell as DATE. Select Column, then FORMAT==Cells==Date. The format dd
mmm yyyy is an option; if you want dd-mmm-yyyy then select CUSTOM and enter
DD-MMM-YYYY.

HTH

"hfazal" wrote:

Hi, I'm writing a Macro, and one of the date columns I want to sort by has
the date in 20060221 format (i.e. 21-Feb-2006), how can I get it a format
that Excel will recognize as a date.

Thanks,
Hussein

This is not helpful. I am trying do this within a macro and also it doesn't
solve the problem of the fact that the data in the cell is not in date format
Feb 5, 2006 in the cell would be 20060205. How do I convert this to be in a
recognizable date format?
--
H


"Toppers" wrote:

Format cell as DATE. Select Column, then FORMAT==Cells==Date. The format dd
mmm yyyy is an option; if you want dd-mmm-yyyy then select CUSTOM and enter
DD-MMM-YYYY.

HTH

"hfazal" wrote:

Hi, I'm writing a Macro, and one of the date columns I want to sort by has
the date in 20060221 format (i.e. 21-Feb-2006), how can I get it a format
that Excel will recognize as a date.

Thanks,
Hussein

Hello,

If your cell imput is in A1, type in B1 :
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

HTH
Cheers
Carim

Hi there,

Have a go with this. Your can change the order of month, day, year in the
DateValue function as you like. Bear in mind that although it checks for 8
characters, a user could still have put this in a different order (ie
20060502 etc.)

Anyway, hope that helps

Best regards

John

Sub TestOddDate()
Dim dProperDate As Date
dProperDate = ConvertOddDate("20060205")
If dProperDate = "00:00:00" Then
MsgBox ("Date not correct length (8)")
Else
MsgBox (dProperDate)
End If
End Sub

Function ConvertOddDate(ByVal sOddDate As String) As Date

Dim sYear As String
Dim sMonth As String
Dim sDay As String

'Trim any spaces
sOddDate = Trim(sOddDate)
'Now check that this contains the correct number of characters
If Len(sOddDate) = 8 Then
sYear = Left(sOddDate, 4)
sMonth = Mid(sOddDate, 5, 2)
sDay = Mid(sOddDate, 7, 2)
ConvertOddDate = DateValue(sMonth & "/" & sDay & "/" & sYear)
End If

End Function


"hfazal" wrote in message
...
This is not helpful. I am trying do this within a macro and also it
doesn't
solve the problem of the fact that the data in the cell is not in date
format
Feb 5, 2006 in the cell would be 20060205. How do I convert this to be in
a
recognizable date format?
--
H


"Toppers" wrote:

Format cell as DATE. Select Column, then FORMAT==Cells==Date. The
format dd
mmm yyyy is an option; if you want dd-mmm-yyyy then select CUSTOM and
enter
DD-MMM-YYYY.

HTH

"hfazal" wrote:

Hi, I'm writing a Macro, and one of the date columns I want to sort by
has
the date in 20060221 format (i.e. 21-Feb-2006), how can I get it a
format
that Excel will recognize as a date.

Thanks,
Hussein