Number separators formula

[Austin_Adesso]

Hey all, we are trying to get unique project numbers for our airtable. (We are managing video projects). Last year, we just did an auto number and had a formula to add 20000 to that number. 20001, 20002, 20003, etc. This year, we want the unique project number to be P21001, P21002, P21003… P for our name, 21 for Year, and 001 for the unique project number. We also want 2020 projects to be P20001, etc.

I found in the forum the formula for getting the YY: MOD(YEAR(CREATED_TIME()), 100)

Can someone help me figure out how to make the formula that will work for any year in this way?

Thanks so much in advance!!
Austin

[Jeremy_Oglesby]

Going off of the foundation @Databaser laid, where {YY Created} = DATETIME_FORMAT(CREATED_TIME(),"YY"):

SWITCH(
   {YY Created},
   "20", {PROJECTs},
   "21", {PROJECTs} - 392
)

That SWITCH() function can be added to in future years by adding a comma at the end of the last statement, and starting a new statement with the "YY", followed by a comma, and then the math required to get the Project # as a 1 for the first project of that year.

[Jeremy_Oglesby]

Ah – looked back and was reminded you want leading zeros on the # part of the Project ID. I’ll post back in a few with that accommodation. I also didn’t actually render the year in there :man_facepalming:

[Jeremy_Oglesby]

SWITCH(
   {YY Created},
   "20", {YY Created} & REPT('0', 4 - LEN({PROJECTs} & '')) & {PROJECTs},
   "21", {YY Created} & REPT('0', 4 - LEN(({PROJECTs} - 392) & '')) & {PROJECTs} - 392
)

@Austin_Adesso - I didn’t actually test that - would you mind plugging it in and verifying if it works?

[Databaser]

Jeremy_Oglesby:

I recently taught my wife, who has no coding experience, how to do some scripting with JavaScript.

It helps when you got someone at home who can teach you in a relevant way I guess :slightly_smiling_face: I still haven’t found my way into it really.