CHANGE TEXT DATE TO NUMERIC DATE

[slf]

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?

 

[Luke M]

It appears that you have the web symbol CHAR(160) in your text. To extract
just the month and year, you can do this:

=LEFT(A2,3)&" "&MID(A2,FIND(CHAR(160),A2,5)-4,4)

 

[Stefi]

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

 

[ryguy7272]

Try this:
Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

 

[Harald Staff]

This little macro might work (or not, date math is very vulnerable to
regional settings). Select the cells in question and run this:


Sub test()
Dim Cel As Range
On Error Resume Next
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Value = DateValue(Cel.Value) + TimeValue(Cel.Value)
Next
End Sub

HTH. Best wishes Harald

 

[Stefi]

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