Re: convert first 6 digits into date


Showing us your code is always helpful. Off the top of my head (and in my tests), I don't see why you should be erroring out on this line...

recserial = Format(Left(SerialRng, 6), "@@/@@/@@")

The only thing I can think of is that the contents of the cell is not in the form you showed us (######-###)... that is, I suspect either one or more leading spaces OR the number in the cell is actually ######### and you have it Custom Formatted as 000000-000. Is either of these the case? If so, tell me which and I will adjust the code accordingly. If not, then tell us what the exact error message that you are getting is.

--
Rick (MVP - Excel)


"tracktraining" <tracktraining@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1322A8A0-AE12-43EB-9425-3D5B0F0F49FE@xxxxxxxxxxxxxxxx
maybe it would help if you see my whole code:

Sub Starting_SerialRng(d1 As Date, d2 As Date, ss As Date, es As Date, p As
String)
Dim SerialRng As Range
Dim myserial As String
Dim recserial As Date

Sheets("Complaint Log (2)").Select
Columns("A:O").Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:=">=" & d1 & "",
Operator:=xlAnd, Criteria2:="<=" & d2 & "", Operator:=xlAnd
Selection.AutoFilter Field:=5, Criteria1:="=*" & p & "*"

With Worksheets("Complaint Log (2)")
Set SerialRng = .Range("D2", .cells(.Rows.count, "D").End(xlUp))
End With

For Each SerialRng In SerialRng.cells
myserial = Left(SerialRng.Offset(0, 3), 6)
recserial = Format(myserial, "@@/@@/@@")
'recserial = Format(Left(SerialRng, 6), "@@/@@/@@")
'recserial = DateSerial(Mid(myserial, 5, 2), Left(myserial, 2),
Mid(myserial, 3, 2))
If IsDate(recserial) Then
If recserial >= ss And recserial <= es Then
SerialRng.EntireRow.Copy
Sheets("Data").Select
Sheets("Data").cells(Rows.count,
1).End(xlUp)(2).PasteSpecial Paste:=xlAll
End If
End If
Next SerialRng

End Sub

--
Learning


"Rick Rothstein" wrote:

Just to be clear, the Format function returns the String value "06/03/08"
and the assignment to the Date variable, or alternately wrapping it in the
CDate function, is what converts that String value into a Date value.

--
Rick (MVP - Excel)


"Mishell" <MishellNospam@xxxxxxxxxx> wrote in message
news:uu9qFLP9JHA.5704@xxxxxxxxxxxxxxxxxxxxxxx
> Thank you Rick.
>
> I did not know that the FORMAT function could do this, transform
> 060308 to 06/03/08
>
> Mishell
>
> "Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message
> news:uY9U0oN9JHA.200@xxxxxxxxxxxxxxxxxxxxxxx
>>> 'You must compare Dates with Dates, not Dates with Strings
>>> 'So forget the Format function if you want to compare dates
>>
>> If you place the output from a **properly constructed** Format >> function
>> into a Date variable, you will have a Date that can be used in a
>> comparison with other dates, so a general warning about staying away >> from
>> the Format function may be too strong a statement. In my response to >> the
>> OP, I offered this code line...
>>
>> MySerial = Left(Format(SerialRng, "@@/@@/@@"), 8)
>>
>> which uses the Format statement as its basis and which works fine (as
>> long as MySerial is Dim'med as a Date). By the way, another way to >> write
>> this statement (so that it looks like a "true" Format statement) is >> this
>> way...
>>
>> MySerial = Format(Left(SerialRng, 6), "@@/@@/@@")
>>
>> and, as long as MySerial is Dim'med as a Date, it would work fine too. >> If
>> the OP wanted to do the comparisons directly, without using a variable
>> Dim'med as a Date, the you would simply wrap the output from the >> Format
>> function with a CDate function call. For example, something like >> this...
>>
>> If CDateFormat(Left(SerialRng, 6), "@@/@@/@@")) = OtherDate Then
>>
>> -- >> Rick (MVP - Excel)
>>
>>
>> "Mishell" <MishellNospam@xxxxxxxxxx> wrote in message
>> news:O%23TDARM9JHA.3544@xxxxxxxxxxxxxxxxxxxxxxx
>>>
>>> Sub Final()
>>>
>>> 'You must compare Dates with Dates, not Dates with Strings
>>> 'So forget the Format function if you want to compare dates
>>>
>>> Dim startserial As Date
>>>
>>> Dim endserial As Date
>>>
>>> startserial = CDate(Me.Start_Serial)
>>> endserial = CDate(Me.End_Serial)
>>>
>>>
>>> For Each SerialRng In SerialRng.Cells
>>> myserial = Left(Cells(SerialRng.Row, 4), 6)
>>> YY = Mid(myserial, 5, 2)
>>> MM = Left(myserial, 2)
>>> DD = Mid(myserial, 3, 2)
>>> record_serial = DateSerial(YY, MM, DD)
>>>
>>> 'On Error Goto Next SerialRng ***(see note below)
>>> If record_serial >= startserial And record_serial <= endserial >>> Then
>>> SerialRng.EntireRow.Copy
>>> Sheets("Data").Select
>>> Cells(Rows.Count, 1).End(xlUp)(2).Select
>>> Selection.PasteSpecial Paste:=xlAll
>>> End If
>>> Next SerialRng
>>>
>>> End Sub
>>>
>>> Mishell
>>>
>>> "tracktraining" <tracktraining@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>>> message news:3D34D2E1-0136-4160-B28F-B487F6AF7873@xxxxxxxxxxxxxxxx
>>>> Hi Everyone,
>>>>
>>>> I have a column that contains serial numbers in this format - >>>> mmddyy -
>>>> ###
>>>> (i.e. 060308-001).
>>>> I would like to split out the first 6 digits and convert it into a >>>> date
>>>> (mm/dd/yy).
>>>> I tried to do this with the following code and it doesn't work:
>>>>
>>>> '------start code
>>>>
>>>> startserial = Format(Me.Start_Serial, "mm/dd/yy")
>>>> endserial = Format(Me.End_Serial, "mm/dd/yy")
>>>>
>>>> For Each SerialRng In SerialRng.cells
>>>> myserial = Left(SerialRng.cells(SerialRng.row, 4), 6)
>>>> record_serial = Format(myserial, "mm/dd/yy")
>>>> On Error Goto Next SerialRng ***(see note below)
>>>> If record_serial >= startserial And record_serial <= endserial >>>> Then
>>>> SerialRng.EntireRow.Copy
>>>> Sheets("Data").Select
>>>> cells(Rows.count, 1).End(xlUp)(2).Select
>>>> Selection.PasteSpecial Paste:=xlAll
>>>> End If
>>>> Next SerialRng
>>>>
>>>> '---- end code
>>>>
>>>> ***here I would like for it to skip to the next SerialRng if cannot >>>> get
>>>> the
>>>> first 6 digit to convert into a date - sometimes the field may not >>>> be a
>>>> serial number and just some other ID number so it can't be converted
>>>> into a
>>>> date.
>>>>
>>>> Please help if possible.
>>>>
>>>>
>>>> Thank you!
>>>> tracktraining
>>>>
>>>>
>>>> -- >>>> Learning
>>>
>>>
>>
>
>



.

Loading