Can the auto-numbering function be set up to utilize new numbering prefix at start of each year?

Sara Ross

I created a sheet that uses auto-numbering to assign a number to all submitted forms. I want the numbering to utilize a prefix denoting the year and start over at 0 each year. For example the numbering of the submissions in year 2021 will start with 2021-01 continuing with 2021-02, 2021-03, etc. When the year 2022 is reached, I would like any new requests to use a 2022 prefix (e.g. 2022-01, 2022-02,...). Is there a way to set this up?

Paul Newcome

The auto-number feature can't do that, but you could use a formula.

Insert a column to track the submitted date (Created Date column) and then in a text/number column use something like this...

=YEAR(Created@row) + "-" + COUNTIFS(Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))

joseply_68

Thanks for the formula for this! I was able to restart numbering for each year based on 'create' field. I have 2 follow-up questions:

1. The formula works if you don't have mulitple items that show created at same date/time - because of the 'countifs' formula, it does not count it as unique - anyway to adjust the formula to avoid this?
2. How do i format the result to be 2024-001 vs. 2024-1 so that when it's sorted, the numbers fall in line?

Thanks!

Paul Newcome

@joseply_68 You can insert an auto-number column that has no special formatting and leverage that. A RIGHT function will take care of the zero-filling.

=YEAR(Created@row) + "-" + RIGHT("000" + COUNTIFS(Created:Created, IFERROR(YEAR(@cell), 0) = YEAR(Created@row), [Auto-Number]:[Auto-Number], @cell<= [Auto-Number]@row), 3)