nde wrote:
> Hi,
>
> what is the significance of -- just before TEXT in the below formula. this
> formula works same even without --.
>
>
> *From:* Sixthsense
>
> *To:* excel-macros@googlegroups.com
> *Sent:* Sun, 29 May, 2011 3:55:33 PM
> *Subject:* Re: $$Excel-Mac
Hi,
what is the significance of -- just before TEXT in the below formula. this
formula works same even without --.
>
>From: Sixthsense
>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 numb
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 Prabhu,
just convert those dates into below format using below formula.
lets say in col A17 thr is date thn in col B17 type this formula.
=TEXT(A17,"mmdd")
A B
5/29/2011 20110529
>
>From: Prabhu
>To: excel-macros@googlegroups.com
>Sent: Sun, 29 May, 2011 9:35:23 AM
>Subject: $$E
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, M
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) wrote:
> *
> =DATE(2000+RIGHT(B2,2),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"
thank you mr sixthsense
may i modif the formula to be:
=DATE(YEAR(
DATEVALUE("1Jan"&RIGHT(B2,2))),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"00"),2))
On Fri, May 13, 2011 at 6:03 PM, Sixthsense wrote:
> Hi Stdev,
>
>
> Your Solution will fail when the person intended to mention the year 19
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))),"")
Herewit
