Auto ID

cself

I'm trying to set an auto ID with prefix = PR followed by the Date and Time in Universal Time Zone, which format should look like PRYYYYMMDDHHMMSS with the hours being 24 hour clock. Any ideas on formula?


jmyzk_cloudsmart_jp

Hi @cself

Thank you for the interesting question.

To generate an Auto ID with the prefix PR followed by the date and time in UTC (Universal Time Zone) in the format PRYYYYMMDDHHMMSS (24-hour clock), you can use the following formulas:

[UTC Auto ID] =[Primary Column]@row + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE([Time Part in 24 format]@row, ":", "")
[Time Part in 24 format] =TIME(RIGHT(Created@row, 8), 1)
[UTC Auto ID w/o Helper Column] =[Primary Column]@row + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE(TIME(RIGHT(Created@row, 8), 1), ":", "")

https://app.smartsheet.com/b/publish?EQBCT=9db01e231840433794c018c93b1780a2

Using a Helper Column:

Helper Column ([Time Part in 24 format]):=TIME(HOUR(Created@row), MINUTE(Created@row), SECOND(Created@row))

Main Formula ([UTC Auto ID]):="PR" + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE([Time Part in 24 format]@row, ":", "")

Explanation:

• YEAR(Created@row): Extracts the year (YYYY).
• RIGHT("0" + MONTH(Created@row), 2): Ensures the month is 2 digits (e.g., "01" for January).
• RIGHT("0" + DAY(Created@row), 2): Ensures the day is 2 digits (e.g., "05").
• SUBSTITUTE([Time Part in 24 format]@row, ":", ""): Converts the time to a compact 24-hour format (HHMMSS) by removing colons.

Without a Helper Column:

Formula ([UTC Auto ID w/o Helper Column]):="PR" + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE(TIME(HOUR(Created@row), MINUTE(Created@row), SECOND(Created@row)), ":", "")

Explanation:

• YEAR(Created@row): Year (YYYY).
• RIGHT("0" + MONTH(Created@row), 2): Month as a 2-digit value.
• RIGHT("0" + DAY(Created@row), 2): Day as a 2-digit value.
• TIME(HOUR(Created@row), MINUTE(Created@row), SECOND(Created@row)): Extracts and formats the time.
• SUBSTITUTE(..., ":", ""): Removes colons from the time to get HHMMSS.

jmyzk_cloudsmart_jp

Hi @cself

Thank you for the interesting question.

To generate an Auto ID with the prefix PR followed by the date and time in UTC (Universal Time Zone) in the format PRYYYYMMDDHHMMSS (24-hour clock), you can use the following formulas:

[UTC Auto ID] =[Primary Column]@row + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE([Time Part in 24 format]@row, ":", "")
[Time Part in 24 format] =TIME(RIGHT(Created@row, 8), 1)
[UTC Auto ID w/o Helper Column] =[Primary Column]@row + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE(TIME(RIGHT(Created@row, 8), 1), ":", "")

https://app.smartsheet.com/b/publish?EQBCT=9db01e231840433794c018c93b1780a2

Using a Helper Column:

Helper Column ([Time Part in 24 format]):=TIME(HOUR(Created@row), MINUTE(Created@row), SECOND(Created@row))

Main Formula ([UTC Auto ID]):="PR" + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE([Time Part in 24 format]@row, ":", "")

Explanation:

• YEAR(Created@row): Extracts the year (YYYY).
• RIGHT("0" + MONTH(Created@row), 2): Ensures the month is 2 digits (e.g., "01" for January).
• RIGHT("0" + DAY(Created@row), 2): Ensures the day is 2 digits (e.g., "05").
• SUBSTITUTE([Time Part in 24 format]@row, ":", ""): Converts the time to a compact 24-hour format (HHMMSS) by removing colons.

Without a Helper Column:

Formula ([UTC Auto ID w/o Helper Column]):="PR" + YEAR(Created@row) + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + SUBSTITUTE(TIME(HOUR(Created@row), MINUTE(Created@row), SECOND(Created@row)), ":", "")

Explanation:

• YEAR(Created@row): Year (YYYY).
• RIGHT("0" + MONTH(Created@row), 2): Month as a 2-digit value.
• RIGHT("0" + DAY(Created@row), 2): Day as a 2-digit value.
• TIME(HOUR(Created@row), MINUTE(Created@row), SECOND(Created@row)): Extracts and formats the time.
• SUBSTITUTE(..., ":", ""): Removes colons from the time to get HHMMSS.

cself

Thank you!!!

jmyzk_cloudsmart_jp

Happy to help!😁