how do i set up a serial number system using the date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

special format for date - 03 Apr 2006 16:35 GMT

I have a file that produces a list of serial numbers depending on the date.
For example, XYZ60403001 is a serial number for a particular widget tested.

XYZ50417001

XYZ is the testing machine,
5 is the year,
04 is the month,
17 is the day of the month, and
001 is the first items tested on that day.

so ABC30704053, would be the 53rd item tested at machine ABC on July 4, 2003.

I would like to create a formula or program that would input the date (in
that format) every time I open the document. I have a file that prints the
serial numbers each morning to labels, but I have to manually change the date
every day for each serial number.

Reply to this Message

Greg - 03 Apr 2006 17:03 GMT

If you could live with a leading zero in the date
e.g., 030704 then a simple formated date field could work

{ Date \@ "yyMMdd" }

If not, and as I know of no date format that uses just a single digit
for the year you could do this with a macro.

Create a bookmark in your document where you want the formatted date to
appear. Call it "Date"

Sub Document_AutoOpen()
Dim myDate As String
Dim oRng As Range
Set oRng = ActiveDocument.Bookmarks("Date").Range
myDate = Format(Date, "yyMMdd")
myDate = Right(myDate, Len(myDate) - 1)
oRng.Text = myDate
ActiveDocument.Bookmarks.Add "Date", oRng
oRng.Collapse wdCollapseEnd
End Sub

Reply to this Message

Peter Jamieson - 03 Apr 2006 18:28 GMT

> { Date \@ "yyMMdd" }
>
> If not, and as I know of no date format that uses just a single digit

{ ={ Date \@YYMMDD } \#00000 }

Hope the questioner is happy with a code structure that recycles in 2010
though!

Peter Jamieson

> If you could live with a leading zero in the date
> e.g., 030704 then a simple formated date field could work
[quoted text clipped - 17 lines]
> oRng.Collapse wdCollapseEnd
> End Sub

Reply to this Message

Greg Maxey - 03 Apr 2006 22:21 GMT

Peter,

Very clever (or maybe obvious depending on perspective). ;-)

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

>> { Date \@ "yyMMdd" }
>>
[quoted text clipped - 28 lines]
>> oRng.Collapse wdCollapseEnd
>> End Sub

Reply to this Message