How to Generate Sequential Excel Codes with Month and Year Values
Question details
The user needs a formula to automatically generate and increment custom sequential codes that include a starting number, month, and year.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating automated tracking codes, invoice numbers, or serial numbers that automatically increment the sequence, month, and year across multiple rows.
- Observed behavior
- A custom sequence formatted as 'Prefix+Sequence / Month / Year' needs to automatically calculate and display the incremented values for each row without manual entry.
Ensure you have determined your desired starting sequence number, starting date, and total number of rows before applying the formulas.
Use a Dynamic Array Formula (LET and SEQUENCE)
This method uses modern dynamic array functions to generate the entire sequence of codes instantly without needing to drag the formula down manually.
By combining LET, SEQUENCE, BASE, DATE, and TEXT functions, you can define your variables once and let the spreadsheet automatically spill the formatted sequence across the specified number of rows.
Click on the specific cell where you want the first sequential code to appear.
Type or paste the following formula into the formula bar: =LET(irows,200,iStart,285,dateStart,DATE(2025,2,1),zSeq,SEQUENCE(irows,1,0,1),"TP"&BASE(zSeq+iStart,10,4)&" / "&TEXT(DATE(YEAR(dateStart),MONTH(dateStart)+zSeq,1),"MM / YY"))
Press Enter. The formula will automatically spill down and populate the next 200 rows with properly incremented numbers, months, and years.
Use Traditional Functions (ROW, MOD, and QUOTIENT)
This alternative is ideal if you are using an older version of Excel or Spreadsheet that does not support dynamic array formulas like SEQUENCE.
Easily Generate Dynamic Sequences with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like LET and SEQUENCE, allowing you to create complex serial numbers effortlessly. It is an excellent choice for seamlessly handling complex spreadsheet calculations.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
- 2. Select the Target Cell: Click on the exact cell where you want the sequential list to begin.
- 3. Paste the Formula: Paste the LET and SEQUENCE formula into the formula bar at the top.
- 4. Adjust Variables: Modify the initial row count (irows), starting sequence (iStart), and date (dateStart) directly in the formula to fit your needs.
- 5. Generate Automatically: Press Enter to instantly generate the full list of sequential codes formatted perfectly.
Frequently Asked Questions
How do I change the prefix from 'TP' to a different string?
Locate the "TP" text string within the formula and replace it with your desired text inside the quotation marks (e.g., "INV" or "CODE").
What if my software version doesn't support LET and SEQUENCE?
If you are using an older spreadsheet version, use the alternative formula provided in the second solution. It uses standard functions like ROW, MOD, and QUOTIENT which are universally supported.
How can I adjust the number of generated codes using the dynamic formula?
In the LET formula, locate the variable 'irows,200'. Change the number '200' to whichever total number of rows you want the sequence to cover (e.g., 'irows,500' for 500 codes).
