Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 24, 2020 12:12 PM
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
Sep 24, 2020 03:51 PM
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")
Sep 24, 2020 05:23 PM
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)
Sep 25, 2020 04:46 AM
Thanks for those hints! I’m super new at this. Where in my equation would I put that so that it adds autonumber?
Sep 25, 2020 10:09 PM
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
Sep 28, 2020 06:44 AM
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!