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: Date Additional numeric number, and 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: 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). XYZ is available from the location column. “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.

Hi @ Christian Stenberg , You can set a column separately for X mark, which needs manual input. =TEXT(Created,"yyddmm")&"."&X&"."&LEFT([Location: City],3) 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.

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

Accepted answer

Yanli Jiang - MSFT 21,361 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)

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.
Please sign in to rate this answer.
chasman 40 Reputation points

2023-03-21T07:39:06.53+00:00

Hi Yanli

Thanks for you help!

Your guide is unfortunatley only partially working for me. I have managed to generate the X and the location parts, but no matter what I seem to try, I cannot get the date format "dd-mm-yyyy" converted into "yymmdd".

To isolate the issue, I have created a separate column for the date format conversion and tried the formulas:

=TEXT(Date,"yymmdd")

and

=TEXT([Date],"yymmdd")

but regardless of whether I choose "Sinlge line of text" or "Date and time" in the "Data type" options, I get the "Sorry something went wrong - the formula contains a syntax error, or is not supported."-message

Any suggestions?

Yanli Jiang - MSFT 21,361 Reputation points Microsoft Vendor

2023-03-21T09:54:38.2433333+00:00

Hi @Christian Stenberg ,

I checked the [Date format] column and it is working fine. Please make sure that the punctuation used in the formula is in English.

chasman 40 Reputation points

2023-03-21T12:54:28.79+00:00

Hi @Yanli Jiang - MSFT

Thanks again for helping me out :-)

Here is a view of the new list I am trying to get the formula to work in:

The "Serienummer"-formula =Date&"."&X&"."&Location is kind of working now, except for the date-format part.

I tried to do a pitstop of the date conversion (hence the "Dato konvertering"-column), to work on isolating the issue, knowing that this is where my root problem is.

If I use =Date or =[Date], it copies the content from the "Date"-column (as shown above and expected)

If I tweak the formular to: =TEXT(Date,"yymmdd") I get this message:

I get this message regardless of whether I tick the "Date and time" or "Single line of text" off in the data type option.

I continue getting the same error message when I

remove the TEXT part of the formula to =Date,"yymmdd", =(Date,"yymmdd") , =[Date],"yymmdd" or =([Date],"yymmdd")

include the TEXT part of the formula to =TEXT([Date],"yymmdd") =TEXT(Date),"yymmdd" or =TEXT(Date,"yymmdd")

use =([Created],"yymmdd")

I even tried to get some json code to do the job, but 8 attempts of chat-GPT code would not do the trick...

Any more suggestions how to overcome the date-format issue??

KR Christian

Yanli Jiang - MSFT 21,361 Reputation points Microsoft Vendor

2023-03-22T02:17:04.6233333+00:00

Hi @Christian Stenberg ,

As I don't have the error like yours, I suggest do some troubleshooting:

1, Create a date column in a new list.

2, Create a new Date and time column in this list.

3, Create a calculated column use TEXT( ) function and see whether the formula works.

For more information about TEXT( ) function, please refer to:

https://support.microsoft.com/en-us/office/text-function-4b46e545-e612-4c84-ac23-edfa68007945

https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3

And I found a post, it said change the ( , ) to ( ; ) in the formula solved his issue, you may have a try.

If you have any questions or progress, you can contact me in time.

chasman 40 Reputation points

2023-03-22T06:41:59.0833333+00:00

Hi @Yanli Jiang - MSFT

It works now! Turns out that it was the change from (,) to (;) that was the cure.

The formula that finally converts dd-mm-yyyy into yymmdd is: =TEKST(Date;"yy")&TEKST(Date;"mm")&TEKST(Date;"dd") ("Single line of text" selected in Data type)

Thanks so much for all your help. It not only fixed my long unsolved date conversion problem, I learned a couple of things along the way :-)

Have a great day

Best regards

Christian

Yanli Jiang - MSFT 21,361 Reputation points Microsoft Vendor

2023-03-22T07:28:29.02+00:00

Hi @Christian Stenberg ,

Great to hear that!

I am very happy the answer is helpful.

Have a nice day!

chasman 40 Reputation points

2023-03-22T07:32:31.3+00:00

PS. Just realized - from your previous suggestions: A simpler formula that does the trick is obviously just: =TEKST(Date;"yymmdd")

Thanks again :-)
Sign in to comment

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

0 additional answers