How to generate a customized serial number from date and contents of a selected column

chasman 40 Reputation points
2023-03-10T10:15:21.1866667+00:00

I would like to automatically generate serial numbers in a Sharepoint list based on the content of selected columns in the list.

The serial number consists of three parts:

  1. Date
  2. Additional numeric number, and
  3. A three-letter combination

A generic sample of the serial code is: yymmdd.x.XYZ where:

"XYZ" is a variable combination of letters representing location within my organization.

"x" represents a chronological differentiation between serial numbers, to avoid copies in case a unique yymmdd and XYZ is repeated.

Serial number Date Location

231003.1.DTX 13-03-2023 DTX

231003.2.DTX 13-03-2023 DTX

231003.1.PRD 13-03-2023 PRD

Sidebar:

  1. The date is available from the list´s date column, but it is automatically generated, and displayed as dd-mm-yyyy. I need the date in the serial number to be reversed and without dash separation (as shown in the example above).
  2. XYZ is available from the location column.
  3. “x” should by default be “1” and incrementally increase to avoid clones of the serial number (as shown in the example above).

Is there a formula that I can use in a calculated cell or is there any other way I can auto-generate this specific serial number? Thanks.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,608 questions
{count} votes

Accepted answer
  1. Yanli Jiang - MSFT 21,371 Reputation points Microsoft Vendor
    2023-03-16T09:57:33.0966667+00:00

    Hi @Christian Stenberg ,

    You can set a column separately for X mark, which needs manual input.

    =TEXT(Created,"yyddmm")&"."&X&"."&LEFT([Location: City],3)
    
    

    03167

    03168


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful