Hi Hanumant Shinde, Slight difference between --TEXT(A2,"YYYYMMDD") & TEXT(A2,"YYYYMMDD"). Whenever you are using the text function with Numeric Value then the value returned by Text Function will be Text Value instead of real number.
TEXT(A2,"YYYYMMDD")>>>>Will return the Text Number. This can be identified easily by the alignment of the value. Text values always stay in Left side of the cell. --TEXT(A2,"YYYYMMDD")>>>>Will return the Real Number. This also can be identified by the alignment of the value. Real Numeric values always stay in Right side of the cell. In Excel we call the two -- as “Unary Operator”. This is equivalent to Value Function. Both --TEXT(A2,"YYYYMMDD") & VALUE(TEXT(A2,"YYYYMMDD")) give the same result and do the same job. OP’s expected result values are represented in the right side of the cell (i.e.) OP expected to derive the Real Number. So I have added the Unary Operator to convert the Text value as real value. Hope it’s clear to you! --- *Sixthsense **:) Man of Extreme & Innovative Thoughts :)* On Mon, May 30, 2011 at 1:52 AM, hanumant shinde <arsfan2...@yahoo.co.in>wrote: > Hi, > > what is the significance of -- just before TEXT in the below formula. this > formula works same even without --. > > > *From:* Sixthsense <sixthsense...@gmail.com> > > *To:* excel-macros@googlegroups.com > *Sent:* Sun, 29 May, 2011 3:55:33 PM > *Subject:* Re: $$Excel-Macros$$ How to change Date format in to number > > Hi Prabhu, > > Try the below formula in cell B2. > > =IF(AND(ISNUMBER(A2),LEN(A2)=5),--TEXT(A2,"YYYYMMDD"),"") > > Drag the B2 cell formula to the remaining cells of B Column based on the > Column-A data. > > At the same time the same is provided in the attached excel for better > understanding. > > Hope that helps! > > --- > *Sixthsense > **:) Man of Extreme & Innovative Thoughts :) > > * > On Sun, May 29, 2011 at 9:35 AM, Prabhu <prabhugate...@gmail.com> wrote: > >> Hi Friends, >> >> I have change my cell which in the date format ex.YYYYMMDD or DDMMYYYY to >> original number i.e smiler to >> type date.I have attached the sheet in which Column A shows date format >> and Column B is what i need to convert A in to B as a number. >> >> Plz help. >> >> Regards >> >> Prabhu >> >> -- >> >> ---------------------------------------------------------------------------------- >> 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 > > -- > > ---------------------------------------------------------------------------------- > 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
