Re: $$Excel-Macros$$ Re:Data - text to date format

2011-04-17 Thread JYH 
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 "ddmm")
3) the date structure as used by your system (could be mmddyy, yymmdd,
ddmmyy, mmdd, etc.)

If you know for sure the structure of the text value provided to you
(in this case, "dd.mm."), 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  wrote:
> try this
>
> =SUBSTITUTE(B3,".","-")-SUBSTITUTE(A3,".","-")
>
> On Sun, Apr 17, 2011 at 5:41 PM, Mohd Sanusi Mohd Noor
> 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 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
> 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

Re: $$Excel-Macros$$ Re:Data - text to date format

2011-04-17 Thread ashish koul 
try this

=SUBSTITUTE(B3,".","-")-SUBSTITUTE(A3,".","-")

On Sun, Apr 17, 2011 at 5:41 PM, Mohd Sanusi Mohd Noor
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 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
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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

Fwd: $$Excel-Macros$$ Re:Data - text to date format

2011-04-17 Thread Mohd Sanusi Mohd Noor 
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 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


date from text to date format.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet