Auto generated number

**drobinson782001** · 11-18-2013, 09:25 PM

how to create a unique number for an account number load. I would like to use time of process, plus a 3 digit record number derived from file. For example, if this file is processed on Nov 02 2013 at 8:15:33 am, the first record should be numbered "JS-20131102081533001" the next record "JS-20131102081533002" and so on PLEASE ADVISE

**vlady** · 11-18-2013, 09:44 PM

if you have those dates and time as real dates and time you can use something like this

="JS - "&TEXT(A1,"yyyymmdd")&TEXT(A1,"hhmmss")&TEXT(ROW(),"0000")

**drobinson782001** · 11-18-2013, 09:51 PM

The date and time is the date the file is processed. That info is not provided within the file, i have to generate it

**hectop** · 11-18-2013, 10:00 PM

The first part can be done with this formula

Please Login or Register  to view this content.

You then need to come up with a seed # for the last 3 digits.
Subsequent values can be calculated as

Please Login or Register  to view this content.

One thing to consider though is that using a formula will cause the date/time values to change. At some point you're going to need to copy this into text. If you're not doing it manually, it's like that you'll need some sort of VBA code. This could be done either via a macro, or a trigger so that when you enter some value on an invoice line, the number is created.