The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

# Re: Unique Project Number Formula Help

Solved
1854 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?

Austin

13 Replies 13
14 - Jupiter

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.

14 - Jupiter

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:

14 - Jupiter
``````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?

12 - Earth

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.