Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Autonumber beginning with date created

Topic Labels: Base design
735 5
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello, I’m looking to use autonumbering, but add the last 2 digits of the year based on date created.
I’m currently using this to manually add “20-” to the beginning of each number. I’d like to automate it so it grabs the date created info.

CONCATENATE(“20-”, REPT( ‘0’, 4-LEN( {autonumber}&’’ ) )& {autonumber})

I have a field called “Date job created”. How do I grab the info from that and plug it into the above formula?

Thanks!
Karen

5 Replies 5

You can use the DATETIME_FORMAT function to get the last two digits of the year from a date field.

DATETIME_FORMAT({Date job created}, "YY")

Welcome to the community, @Karen_Siugzda! :grinning_face_with_big_eyes: Another option is to use the YEAR() function, convert it to a string, then take the right two characters:

RIGHT(YEAR({Date job created}) & "", 2)

Thanks for those hints! I’m super new at this. Where in my equation would I put that so that it adds autonumber?

That depends. Your original message doesn’t indicate where that two-digit year indicator belongs related to the other pieces.

I notice that you’re using both types of concatenation in your formula: the CONCATENATE() function, and the & operator. My preference is to only use the latter, primarily because it’s shorter. For example:

thing1 & thing2 & thing3

…will always be shorter than:

CONCATENATE(thing1, thing2, thing3)

The savings becomes even more apparent when you’re concatenating items inside a function, the output of which is concatenated to something else, etc. Frankly, I don’t know why the CONCATENATE() function exists. Its output is identical to that of the & operator, and the operator is so much shorter.

Anyway, taking your original example above and rewriting it to only use the & operator, it would look like this:

"20-" & REPT("0", 4-LEN({autonumber} & "")) & {autonumber}

Going back to your “where would I put that” question, you can insert that anywhere you want, as long as the final pattern is…

thing1 & thing2 & thing3 & ... & thingN

Awesome! Thanks for that tip as well!
Here’s my completed formula:

RIGHT(YEAR({Date job created}) & “”, 2) & “-” & REPT(“0”, 6-LEN({autonumber} & “”)) & {autonumber}

which gives me a numbering system like this:
20-000077
20-000078

And when the new year comes, it will automatically begin using “21” (instead of “20”) for all new job numbers and simply continue appending the autonumber to the end.

Thanks for all your assistance!