=(TEXT(RIGHT(A1,4),"####")&(TEXT(LEFT(A1,4),"####")))

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does any one know how to make this affect a selected range? What I'm trying to do is take '20031216' and turn it into '12162003'. It only has to work with 8 digit numbers (dates). I'll admit I don't know what I'm doing, but the following code clears the selected cells. Or, is there a better way to take text dates(20031216) and turn them into something Excel can use as a date

On Error Resume Nex
Dim cel As Rang
Dim myVar As Rang
Set myVar = Selectio

For Each cel In myVa
If Left((Trim(cel)), 4) = "2003" The
cel.Value = Text(Right(cel, 4), "####") & (Text(Left(cel, 4), "####")
End I
Nex

With myVa
.NumberFormat = "########);[Red](########)
.Columns.AutoFi
End Wit
End Su

Any help would be appreciated.
 
Rik

Try This
=DATE(RIGHT(A26,4),MID(A26,5,2),RIGHT(A26,2))

If you want to write a macro use the DateSerial funtion in
place of Excel'x DATE function

Regards
Peter
-----Original Message-----
Does any one know how to make this affect a selected
Click to expand...
range? What I'm trying to do is take '20031216' and turn
it into '12162003'. It only has to work with 8 digit
numbers (dates). I'll admit I don't know what I'm doing,
but the following code clears the selected cells. Or, is
there a better way to take text dates(20031216) and turn
them into something Excel can use as a date?
On Error Resume Next
Dim cel As Range
Dim myVar As Range
Set myVar = Selection

For Each cel In myVar
If Left((Trim(cel)), 4) = "2003" Then
cel.Value = Text(Right(cel, 4), "####") & (Text (Left(cel, 4), "####"))
End If
Next

With myVar
.NumberFormat = "########);[Red](########)"
.Columns.AutoFit
End With
End Sub

Any help would be appreciated.
.
Click to expand...
 
=DATE(RIGHT(A26,4),MID(A26,5,2),RIGHT(A26,2))

so/be =DATE(LEFT(A26,4),MID(A26,5,2),RIGHT(A26,2))

I know Peter would agree.


Peter Atherton said:
Rik

Try This
=DATE(RIGHT(A26,4),MID(A26,5,2),RIGHT(A26,2))

If you want to write a macro use the DateSerial funtion in
place of Excel'x DATE function

Regards
Peter
-----Original Message-----
Does any one know how to make this affect a selected
Click to expand...
range? What I'm trying to do is take '20031216' and turn
it into '12162003'. It only has to work with 8 digit
numbers (dates). I'll admit I don't know what I'm doing,
but the following code clears the selected cells. Or, is
there a better way to take text dates(20031216) and turn
them into something Excel can use as a date?
On Error Resume Next
Dim cel As Range
Dim myVar As Range
Set myVar = Selection

For Each cel In myVar
If Left((Trim(cel)), 4) = "2003" Then
cel.Value = Text(Right(cel, 4), "####") & (Text (Left(cel, 4), "####"))
End If
Next

With myVar
.NumberFormat = "########);[Red](########)"
.Columns.AutoFit
End With
End Sub

Any help would be appreciated.
.
Click to expand...
Click to expand...
 
Thanks guys! It took about an hour for me to guess the syntax, but I got it & it works great. I really appreciate it.
 
If your data is in a single column, you may want to record a macro when you do
Data|Text to columns. You can specify that's it's a date in ymd format. After
that, you can just format it as a custom date mmddyyyy.



Rik said:
Does any one know how to make this affect a selected range? What I'm trying to do is take '20031216' and turn it into '12162003'. It only has to work with 8 digit numbers (dates). I'll admit I don't know what I'm doing, but the following code clears the selected cells. Or, is there a better way to take text dates(20031216) and turn them into something Excel can use as a date?

On Error Resume Next
Dim cel As Range
Dim myVar As Range
Set myVar = Selection

For Each cel In myVar
If Left((Trim(cel)), 4) = "2003" Then
cel.Value = Text(Right(cel, 4), "####") & (Text(Left(cel, 4), "####"))
End If
Next

With myVar
.NumberFormat = "########);[Red](########)"
.Columns.AutoFit
End With
End Sub

Any help would be appreciated.
Click to expand...
 
Back
Top