Covert the Date in text format to Date format

[Shamsuddeen]

Hi all!


In my worksheet, A1 houses the date in text format. eg. 31.03.2004 which I want to convert to Date format by using VBA.

The following formula does the same without macro

B1= =date(right(A1,4),mid(A1,4,2),left(A1,2))

When I tried the above formula in macro it did not work. So please help me to get something like:

[B1] = ?????? (A1)


Regards,

Shamsuddeen

 

[tactps]

Try:

ActiveCell.Formula = _
"=DATE(RIGHT(a1,4),MID(a1,4,2),LEFT(a1,2))"

 

[phantom1975]

Have you tried this:

Range("B1").Value = CDate(Range("A1").Value)

 

[phantom1975]

I really should have tested before I posted. This worked for me:

Range("B2").Value = Format(Mid(Range("A1").Value, 4, 2) & "/" & Left(Range("A1").Value, 2) & "/" & Right(Range("A1").Value, 4), "mm/dd/yyyy")

 

[Shamsuddeen]

ADVERTISEMENT

Works Great and many thanks.

Could you please tell me how to make a UDF for the same purpose


Function DateConvert(aaa)

DateConvert = ?????????????????


End Function


Regards,

Shamsuddeen

 

[phantom1975]

Function MyDate (OrigDate as Variant) as Date

MyDate = Format(Mid(OrigDate, 4, 2) & "/" & Left(OrigDate, 2) & "/" & Right(OrigDate,4),"mm/dd/yyyy")m

End Function

It's untested because I gotta go to bed.

 

[Shamsuddeen]

Thanks Mr. Phantom

It works. But it give the date as a date serial number. I have to format it as date. Can the format be done thru the same UDF eg. dd-mmm-yyyy


Regards,

Shamsuddeen