UDF to convert Date

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292
Hi everybody,


I have the following UDF to convert the dates from text format (eg:- 22.02.2004) to Date format. (Text format dates are imported from other software to excel and there are more than 500 such dates)

Function MyDate(TextDate) As Date

MyDate = Left(TextDate, 2) & "-" & Mid(TextDate, 4, 2) & "-" & Right(TextDate, 4)

End Function


The above UDF works fine but it returns the date as a date serial number which again I have to format as dd-mmm-yyyy thru Format...Cells.....

Is it possible to display the date in dd-mmm-yyyy format thru the above UDF instead of showing it as a serial number?


Regards,

Shamsuddeen
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
I think you must have submitted the message twice, which you realised and deleted?
 

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292

ADVERTISEMENT

Iridium said:
I think you must have submitted the message twice, which you realised and deleted?

Yes, you are right. I submitted more than one time due to some eror while posting and when I noticed, I deleted.



Regards,
Shamsuddeen
 

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
Shamsuddeen said:
Iridium said:
I think you must have submitted the message twice, which you realised and deleted?

Yes, you are right. I submitted more than one time due to some eror while posting and when I noticed, I deleted.



Regards,
Shamsuddeen

That's OK - as to your question - try using the macro recorder to generate the code to apply the necessary code you want.

HTH
 

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292

ADVERTISEMENT

Hi all,

Still awaiting for any help please.

Regards,

Shamsuddeen
 

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292
Hi Iridium,

When I recorded the macro, it retuned me the following:

ActiveCell.NumberFormat = "dd-mmm-yyyy"


But how do I incorporate the same to the UDF?



Shamsuddeen
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
Actually, as far as I'm aware, you can't use a UDF to change the format of a cell - only to obtain a value.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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