Help

Unique Project Number Formula Help

Topic Labels: Formulas
Solved
Jump to Solution
2594 13
cancel
Showing results for 
Search instead for 
Did you mean: 
Austin_Adesso
5 - Automation Enthusiast
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.
Screen Shot 2021-01-08 at 9.47.49 AM

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

13 Replies 13

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.

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:

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?

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.