That solution, although not entirely wrong, would not work on all computers.
This is mainly caused because there are many potential problems : 1) the date items separator (here ".") 2) the date structure of the text field (here it is "ddmmyyyy") 3) the date structure as used by your system (could be mmddyy, yymmdd, ddmmyy, mmddyyyy, etc.) If you know for sure the structure of the text value provided to you (in this case, "dd.mm.yyyy"), I always suggest you make sure you build the right excel internal date structure like that: =DATE(RIGHT(B3;4);MID(B3;4;2);LEFT(B3;2))- DATE(RIGHT(A3;4);MID(A3;4;2);LEFT(A3;2)) On 17 avr, 10:21, ashish koul <koul.ash...@gmail.com> wrote: > try this > > =SUBSTITUTE(B3,".","-")-SUBSTITUTE(A3,".","-") > > On Sun, Apr 17, 2011 at 5:41 PM, Mohd Sanusi Mohd Noor > <sanus...@gmail.com>wrote: > > > > > > > > > > > > > Dear all, > > > Would like to seek your kind assistance/help on the date format. > > I've a data on date derive from SAP, when transfer to excel the date is in > > text format. > > Is there any way/method to change the dates from text to date format. > > > Attached is the sample data for reference. > > > The assistance on the above is highly appreciated. > > > Tq > > > Sanusi > > > -- > > > ---------------------------------------------------------------------------------- > > 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 athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > 5. Excel Tips and Tricks athttp://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 > > -- > *Regards* > * * > *Ashish Koul* > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > *akoul*.wordpress.com <http://akoul.wordpress.com/> > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> > > P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- 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
