Prev: How to insert lines into a workbook without changing references?
Next: use Find without hardcoded value but a value from a cell
From: Stefi on 5 Jan 2010 05:20
On Jan 4, 10:11 pm, Stefi <csoszp...(a)gmail.com> wrote:
> On jan. 4, 21:41, slf <s...(a)discussions.microsoft.com> wrote:
>
>
>
>
>
> > The following imported data isn't recognized as a dates:
>
> > Jul  4 2008  6:30AM
> > Jun 22 2007  5:59PM
>
> > I have tried both of these formulas found elsewhere in the Excel Community
> > without luck:
>
> > =DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
>
> > =TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
> > .......................
> > I need the month and year only, text to columns work but requires the data
> > to be copied elsewhere first.  
> > using "=left" function can provide the month, but the placement of the year
> > varies by 1 character in the middle of the cell, so "=mid" can't be utilized.
>
> > I have used "=trim" function and tried the "=Datevalue" function also.
>
> > Any suggestions?
>
> Try this:
>
> =DATEVALUE(SUBSTITUTE(LEFT(TRIM(A1),SEARCH("/",SUBSTITUTE(TRIM(A1),"
> ","/",3)))," ","/"))
>
> Not tested, because US Regional settings and English language Excel is
> needed to properly evaluate this formula, I have a national language
> version, but it should work.
>
> Regards,
> Stefi- Hide quoted text -
>
> - Show quoted text -

This is tested:
=DATEVALUE(MID(A2,4,3)&"/"&LEFT(A2,3)&"/"&RIGHT(LEFT(TRIM($A$2),SEARCH
("/",SUBSTITUTE(TRIM($A$2)," ","/",3))-1),4))
It requires English Regional settings and English language Excel
version.

Regards,
Stefi
First  |  Prev  | 
Pages: 1 2
Prev: How to insert lines into a workbook without changing references?
Next: use Find without hardcoded value but a value from a cell