- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 08, 2021 08:55 AM
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
Solved! Go to Solution.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 08, 2021 12:02 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 08, 2021 12:04 PM
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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 08, 2021 12:10 PM
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 08, 2021 11:39 PM
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.

- « Previous
-
- 1
- 2
- Next »