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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,080
Messages
5,857,234
Members
431,864
Latest member
Raul Ruan

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