Auto ID
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?
Best Answer
-
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), ":", "")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.
Answers
-
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), ":", "")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.
-
Thank you!!!
-
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!