How to automatically generate document number in excel

The form that only adds data is the most basic form. Some document forms with fixed content will be more complicated. In the documents produced by Excel, some contents need to automatically generate some data for the sake of continuity and authenticity to facilitate recording. We For frequently seen invoices, the machine-printed invoice numbers are automatically consecutive to prevent tax evasion. How are these document numbers automatically generated? Let’s listen to the explanation below.

For example, when generating the number of the outbound order, a combination of English letters and numbers is usually used. The English letter part of the number may use the capital form of the first letter of the document type. For example, the outbound document can be prefixed with CK.

　

Method 1: Use the simplest rand function, which can generate a random number between 0-1. Then our encoding can be completed as follows:

1. Multiply the rand function by 1000 in order to change the integer part into 3 digits.

2, and then obtain it through the round function. The integer part with 0 decimal places

3. The overall formula of connecting "CK" English letters through the connector & is: "NO.CK-"&ROUND(RAND()*1000,0).

　

Method 2: Use the today function of the date function to obtain the year, month and day of the current day as the code.

Then our coding can be completed as follows:

1. Use today to get the current date

2. Then use the text function to get today’s date. The year, month and day are displayed

3, and the English letters "CK" are connected through the connector &. The overall formula is: =="NO.CK-"&TEXT(TODAY(),"yymmdd") .

　

Method 3: Use the now function of the date function to obtain the real-time time of the day as the encoding.

Then our coding can be completed as follows:

1. Use now to get the current time. This function contains the year, month, day, hour, minute, second, etc.

2. Then use the text function to display the specific numbers of year, month, day, hour, minute and second, without displaying symbols such as "-" and ":"

3. Connect the English letters "CK" through the connector &

The overall formula is: ="NO.CK-"&TEXT(NOW(),"yymmddhhmmss").

　

Method 4: Use the now function of the date function combined with the today function to obtain the decimal part of the time number.

Then our coding can be completed as follows:

1. Use now to get the current time. This function contains the year, month, day, hour, minute, second, etc.

2. Use today to get the current date

3. Subtract the two to get the decimal part (specifically hours, minutes and seconds)

4. Then use the round function to obtain 3 decimal places and then multiply with 1000

5. Connect the "CK" English letters

through the connector &. The overall formula is: =="NO.CK-"&ROUND((NOW()-TODAY()), 3)*1000.

　

The above describes several methods of automatically generating numbers. Depending on the settings, the content of the generated numbers is also different. The format content can be selected according to the requirements of actual work. After learning this method, we can also set numbers in our own journals for statistics in the future, and office software will also be used in daily life.

The above is the detailed content of How to automatically generate document number in excel. For more information, please follow other related articles on the PHP Chinese website!