Forum Discussion
How to write a formula for generating a number automatically based on year and running number
Hi There,
Do you know the formula for generating a number automatically based on year and a running number? This generated number should be automatic and every year, it needs to start with that year number and at 0001.
For example, I would like to start the numbering with 2023-0001 (four digits for numbering is a must).
The generated sequence must go like below.
2023-0001
2023-0002
2023-0003 ........
.
.
.
2024-0001 (when the calendar year changes, year number and the running number should change).
2024-0002
2024-0003.....
Thanks,
Santhi
Do you know the formula for generating a number automatically based on year and a running number?
First of all, there's not a single "the formula" for doing that. There could be several such formulas, with the context in which you want these numbers--which will actually be texts--to appear being a big factor in how the appropriate one would work. Is there going to be a single table in which each new row generates a new serial number as you've specified? Will there be an associated transaction with it--e.g., a new order--and a date for that transaction, such that the date could be drawn from that date?
But anyway, attached is one example of how it could be done. You could keep column B hidden (you'll need to copy the formula down further) and just let it work behind the scenes.
Santhi_Adusumilli Try the following formula-
=MAP(A4:INDEX(A4:A50000,COUNTA(A4:A50000)),LAMBDA(x,YEAR(x)&"-"&RIGHT("0000"&SUM(--(YEAR(A4:x)=YEAR(x))),4)))
