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

For Each SerialRng In SerialRng.cells
myserial = Left(SerialRng.cells.offset, 6)
record_serial = DateSerial(mid(myserial,5,2),
Left(Myserial,2),mid(myserial,3,2) )
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


"tracktraining" wrote:

> 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

the code is erroring out at this location:
record_serial = DateSerial(mid(myserial,5,2),
Left(Myserial,2),mid(myserial,3,2) )

During these two lines (see below), i am trying to 1.) read the first 6
digits then 2.) convert to format mm/dd/yy. Maybe it would help if i told you
that the serial number is in column is D.

myserial = Left(SerialRng.cells(SerialRng.row, 4), 6)
record_serial = Format(myserial, "mm/dd/yy")

thanks!
--
Learning


"Joel" wrote:

> For Each SerialRng In SerialRng.cells
> myserial = Left(SerialRng.cells.offset, 6)
> record_serial = DateSerial(mid(myserial,5,2),
> Left(Myserial,2),mid(myserial,3,2) )
> 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
>
>
> "tracktraining" wrote:
>
> > 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

The offset satement was wrong. the code below should work. I create a set
statement so you can see how the code should work.

Set SerialRange = Range("A1:A10")
For Each SerialRng In SerialRange
myserial = Left(SerialRng.Offset(0, 3), 6)
record_serial = DateSerial(Mid(myserial, 5, 2), _
Left(myserial, 2), Mid(myserial, 3, 2))
Next SerialRng


"tracktraining" wrote:

> the code is erroring out at this location:
> record_serial = DateSerial(mid(myserial,5,2),
> Left(Myserial,2),mid(myserial,3,2) )
>
> During these two lines (see below), i am trying to 1.) read the first 6
> digits then 2.) convert to format mm/dd/yy. Maybe it would help if i told you
> that the serial number is in column is D.
>
> myserial = Left(SerialRng.cells(SerialRng.row, 4), 6)
> record_serial = Format(myserial, "mm/dd/yy")
>
> thanks!
> --
> Learning
>
>
> "Joel" wrote:
>
> > For Each SerialRng In SerialRng.cells
> > myserial = Left(SerialRng.cells.offset, 6)
> > record_serial = DateSerial(mid(myserial,5,2),
> > Left(Myserial,2),mid(myserial,3,2) )
> > 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
> >
> >
> > "tracktraining" wrote:
> >
> > > 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

the date is
Dim mydate As Date
mydate = DateSerial(Mid(Range("A1"), 5, 2), Mid(Range("A1"), 3, 2),
Left(Range("A1"), 2))

so for your loop

For Each SerialRng In SerialRng.cells
record_serial= Left(Range("A1"), 2) & "/" & Mid(Range("A1"), 3, 2) &
"/" & Mid(Range("A1"), 5, 2)
if isdate(record_serial) then
If record_serial >= startserial And record_serial <= endserial Then
SerialRng.EntireRow.Copy
Sheets("Data"). cells(Rows.count,
1).End(xlUp)(2).PasteSpecial Paste:=xlAll
End If
End If
Next


"tracktraining" <(E-Mail Removed)> wrote in message
news:3D34D2E1-0136-4160-B28F-(E-Mail Removed)...
> 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

Here is another method you can use to convert your serial numbers into real
dates...

MySerial = Left(Format(SerialRng, "@@/@@/@@"), 8)

This code line requires MySerial to be Dim'med as a Date (so that VB will
convert the String value on the right side into a real date. If you want to
force this conversion (instead of allowing VB to do the conversion behind
the scenes), then use this...

MySerial = CDate(Left(Format(SerialRng, "@@/@@/@@"), 8))

--
Rick (MVP - Excel)


"tracktraining" <(E-Mail Removed)> wrote in message
news:3D34D2E1-0136-4160-B28F-(E-Mail Removed)...
> 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

Oh, and the statement I posted also assumes your regional settings are set
for dates in month, date, year order (so the mm/dd/yy ordering of the String
that results from my statement will be interpreted correctly).

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Here is another method you can use to convert your serial numbers into
> real dates...
>
> MySerial = Left(Format(SerialRng, "@@/@@/@@"), 8)
>
> This code line requires MySerial to be Dim'med as a Date (so that VB will
> convert the String value on the right side into a real date. If you want
> to force this conversion (instead of allowing VB to do the conversion
> behind the scenes), then use this...
>
> MySerial = CDate(Left(Format(SerialRng, "@@/@@/@@"), 8))
>
> --
> Rick (MVP - Excel)
>
>
> "tracktraining" <(E-Mail Removed)> wrote in message
> news:3D34D2E1-0136-4160-B28F-(E-Mail Removed)...
>> 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
>

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" <(E-Mail Removed)> wrote in message
news:3D34D2E1-0136-4160-B28F-(E-Mail Removed)...
> 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

> '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" <(E-Mail Removed)> wrote in message
news:O%(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
> news:3D34D2E1-0136-4160-B28F-(E-Mail Removed)...
>> 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
>
>

Thank you Rick.

I did not know that the FORMAT function could do this, transform
060308 to 06/03/08

Mishell

"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>> '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" <(E-Mail Removed)> wrote in message
> news:O%(E-Mail Removed)...
>>
>> 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" <(E-Mail Removed)> wrote in
>> message news:3D34D2E1-0136-4160-B28F-(E-Mail Removed)...
>>> 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
>>
>>
>