. 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
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
--
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
: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
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!
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
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
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
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:
*
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
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
11 matches
Mail list logo