How to create a calculated field to increment values

Question

We have a SharePoint list which contains a column used to identify the row (basically it's a ticket # for support). This column uses the year, month, day, and then an incrementing number. The technicians have to calculate this value, but we need the incrementing number to be the ID of the row, which of course we can't get until the row is created. I know we need to create a calculated column for this, but how would we increment this value so that if a tech created a row, it would be, say, a value of 100. Another tech enters a row and the value is 101. It would look like year/day/month - calculated ID. I don't know why this is so difficult to accomplish. We need the SharePoint list to do this as the column is fed into a PowerAutomate workflow and we don't want to have to modify the workflow to create the value. Any help would be appreciated.

Answer 1

Hi @Richard Duane Wolford Jr ,

I understand that you want to create a calculated column in SharePoint that generates a unique identifier for each row based on the year, month, day, and an incrementing number.

We can use the built-in ID column that SharePoint automaticaly assigns to each item in a list. This column is hidden by default, but you can make it visible in the current view. The ID column is a number that starts from 1 and increments by 1 for each new item. You can use this column as the basis for your calculated column.

To create your calculated column, follow these steps:

• Go to your SharePoint list and click on the settings gear icon. Select “List settings” from the drop-down menu.
• Under “Columns”, click on “Create column”.
• Give your column a name, such as “Ticket #”, and select “Calculated” as the column type.
• In the formula box, enter the following expression:

=TEXT(Created,"yyyy/dd/mm")&"-"&TEXT(ID,"000")

This formula will concatenate the year, month, and day of the item creation date with a dash and the ID number padded with zeros to make it three digits long. For example, if an item was created on September 01, 2022 and its ID was 1, the formula would return “2022/01/09-001”.

If you want the ID to count from 100, but the built-in ID column is 1, you can use the following formula (add "+99" , change it as you see fit)

=TEXT(Created,"yyyy/dd/mm")&"-"&TEXT(ID+99,"000")

The formula would return “2022/01/09-100”.

• Click on “OK” to save your column.

Hope the information can help you. And if there have any unclear or misunderstanding, please feel free to post back and we’ll continue to help you all the time!

---

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.

Answer 2

Creating a calculated field to increment values in a SharePoint list can indeed be a valuable addition, especially when it comes to managing unique identifiers, such as support ticket numbers. To achieve this, you can utilize a formula in the calculated field that will automatically increment the values based on predefined logic.

One way to approach this is by using a formula

=IF(ISBLANK([calculated field name]), 1, [calculated field name] + 1)

This formula checks whether the calculated field is empty. If it is indeed empty, it sets the field's value to 1. If the field contains data, it increments the value by 1. This method provides an automated way to manage incrementing ticket numbers within your SharePoint list. To put this into practice, navigate to your SharePoint list where you want to create the calculated field. Access "List Settings." Under "Columns," select "Create Column." Choose the "Calculated" column type and assign a name to it. In the "Formula" field, input the formula mentioned above. Save your settings.

Once the calculated field is created, you can effortlessly integrate it into your SharePoint list, allowing it to increment values as intended.

For instance, in the context of a customer support ticket list, your "Ticket Number" field can employ the formula to automatically generate and increment unique ticket numbers. If, for instance, the last ticket number was 100, the next created ticket will be assigned the number 101.

Furthermore, you can seamlessly integrate this calculated field into your Power Automate workflows. This means that when you create a new support ticket through a workflow, the ticket number will automatically increment as you've defined in the SharePoint calculated field. This simplifies your process and ensures that unique identifiers are efficiently managed.