A simpler and more direct way would be to use Text To Columns by selecting your date column, and in the column data format, select DMY and finish the conversion. All your text dates would now be actual dates, and you don't require use of any formula.
Alternatively, if you would like to use formula, try this
=IF(ISNUMBER(A2),A2,
DATE(RIGHT(A2,4),SUBSTITUTE(MID(A2,SEARCH("/",A2)+1,2),"/",""),SUBSTITUTE(LEFT(A2,2),"/",""))
)
Regards,
Sam Mathai Chacko (GL)
On Tue, Oct 11, 2011 at 9:01 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:
> Dear Suman,
>
> if you interested to use VBA then you can use...
>
> *Function Date_format(dt As Date)
> Date_format = VBA.Format(dt, "mm/dd/yyyy")
> End Function
> *
> --
> Thanks & regards,
> Noorain Ansari
> *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>
> On Tue, Oct 11, 2011 at 6:38 PM, Suman <ksuman1...@gmail.com> wrote:
>
>> Hi,
>> Good evening to all,
>> Please help in changing date formate.
>>
>> --
>>
>>
>>
>>
>>
>>
>>
>> «•´`•.(*•.¸(`•.¸*¸.•´)¸.•*).•´`•»
>> «´¨*•.¸¸.*.....~*--Suman --*~......*.¸¸.•*¨`»
>> «•´`•.(¸.•´(¸.• * •.¸)`•.¸).•´`•»
>>
>> Mob: +91 9810333884
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
<<A_html_m691da4f3.gif>>
