Covert the Date in text format to Date format

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Try:

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

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

Active Member
Joined
Feb 16, 2002
Messages
292

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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

Active Member
Joined
Feb 16, 2002
Messages
292
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,575
Messages
5,596,961
Members
414,114
Latest member
Lost_User21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top