Re: $$Excel-Macros$$ How to change Date format in to number

2011-05-30 Thread Sixthsense
. 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

$$Excel-Macros$$ How to change Date format in to number

2011-05-29 Thread Prabhu
Hi Friends, I have change my cell which in the date format ex.MMDD or DDMM 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 --

Re: $$Excel-Macros$$ How to change Date format in to number

2011-05-29 Thread Mahesh parab
Hi Try Sub Format() Dim LR, i As Long LR = Range(A Rows.Count).End(xlUp).Row For i = LR To 2 Step -1 Range(A i).Offset(0, 1).Value = Range(A i).Value Range(A i).NumberFormat = mmdd Range(A i).Offset(0, 1).NumberFormat = mmdd Next i End Sub Thanks Mahesh On Sun, May 29, 2011 at

Re: $$Excel-Macros$$ How to change Date format in to number

2011-05-29 Thread hanumant shinde
:23 AM Subject: $$Excel-Macros$$ How to change Date format in to number Hi Friends, I have change my cell which in the date format ex.MMDD or DDMM 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

Re: $$Excel-Macros$$ How to change Date format in to number

2011-05-29 Thread Sixthsense
Hi Prabhu, Try the below formula in cell B2. =IF(AND(ISNUMBER(A2),LEN(A2)=5),--TEXT(A2,MMDD),) 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!

Re: $$Excel-Macros$$ How to change Date format in to number

2011-05-29 Thread hanumant shinde
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

Re: $$Excel-Macros$$ How to change Date format

2011-05-13 Thread Sixthsense
Hi Prabhu, Try the below formula and format the cell as date. =IF(AND(OR(LEN(TRIM(B2))=5,LEN(TRIM(B2))=6),ISNUMBER(B2)),IF(LEN(TRIM(B2))=5,--(--MID(TRIM(B2),2,2)---LEFT(TRIM(B2),1)---MID(TRIM(B2),4,2)),--(--MID(TRIM(B2),3,2)---LEFT(TRIM(B2),2)---MID(TRIM(B2),5,2))),) Herewith I have attached

Re: $$Excel-Macros$$ How to change Date format

2011-05-13 Thread STDEV(i)
thank you mr sixthsense may i modif the formula to be: =DATE(YEAR( DATEVALUE(1JanRIGHT(B2,2))),MID(TEXT(B2,00),3,2),LEFT(TEXT(B2,00),2)) On Fri, May 13, 2011 at 6:03 PM, Sixthsense sixthsense...@gmail.com wrote: Hi Stdev, Your Solution will fail when the person intended to

Re: $$Excel-Macros$$ How to change Date format

2011-05-13 Thread Sixthsense
Hi Stdev, Your Solution will fail when the person intended to mention the year 1998 in this manner 10498. --- *Sixthsense **:) Man of Extreme Innovative Thoughts :)* On Fri, May 13, 2011 at 4:13 PM, STDEV(i) setiyowati.d...@gmail.com wrote: *

$$Excel-Macros$$ How to change Date format

2011-05-13 Thread Zafar Iqbal
Excellent formula. Well Done. Regards, ZAFAR IQBAL From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Sixthsense Sent: Friday, May 13, 2011 12:13 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ How to change Date format Hi Prabhu, Try

$$Excel-Macros$$ How to change Date format

2011-05-12 Thread Prabhu
Hi friends, Plz help to change date format when downloaded report from 1st of every month to 9th of the month will be like DMMYY . I have no issue in changing the format using text to column from 10th date because it will be in DDMMYY. I have attached the sample sheet for your reference.Plz