Sequential Number

KempenUSA

I am creating a Project Numbering system in a sheet that are generated based on selected criteria's. The last 3 digits would need to be sequential numbers "000"

Auto-numbering will not work for this application as the criteria's differ for each program.

Follow is what the formula looks like in excel:

&TEXT(COUNTIF($B$2:B3,B4)+1,"000")

An example of how the number looks at the end is:

Paul Newcome

Try something along the lines of

="C539-50-G" + IF(COUNTIFS($B$2:B3, B4) < 10, "00", IF(COUNTIFS($B$2:B3, B4) < 100, "0")) + COUNTIFS($B$2:B3, B4)

KempenUSA

great Thx, will try this.

Paul Newcome

Happy to help. 👍️

APalani

Hi Paul,

I have similar request to create a auto number, sequence is

"ECR- 2021-8-0001": ECR- Year- Month- 0000

I am able to pull the month from the date it the log was created, but not able to update the last batch.

If its September: ECR-2021-09-0001

I use "ECR-2021" + "-" + MONTH(Created1) formula , but need to input sequence numbering

eg: ECR-2021-8-0001

ECR-2021-8-0002

ECR-2021-9-0001

ECR-2021-9-0002

Can you suggest better way to fill it up?

APalani

https://community.smartsheet.com/discussion/comment/263061#Comment_263061

This is what i tried but keep getting same value for the month. Is there any better solution?

Paul Newcome

@APalani Try something like this...

="ECR-2021" + "-" + MONTH(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10, "000", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 100, "00", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 1000, "0"))) + COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))

APalani

Paul, thanks alot , it works really good. I tried on a log with same day/time, it generated different ECR number.

But I was asked to include the date as well. I want to know if its feasible.

ECR-Year-Month-Date00

In above format Thanks!

Paul Newcome

To include the day it would be this:

="ECR-2021" + "-" + MONTH(Created@row) + "" + DAY(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10, "000", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 100, "00", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 1000, "0"))) + COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))

APalani

Thank you so much Paul, the function works well.

APalani

Paul, one request: is it possible to increment based on the date

For eg:

ECR-2021-08-23-0001

ECR-2021-08-24-0001

ECR-2021-08-24-0002

something like this.

Paul Newcome

Yes. This should work for you...

="ECR-" + YEAR(Created@row) + "-" + IF(MONTH(Created@row) < 10, "0") + MONTH(Created@row) + "-" + IF(DAY(Created@row) < 10, "0") + DAY(Created@row) + "-" + IF(COUNTIFS(Created:Created, @cell = Created@row) < 10, "000", IF(COUNTIFS(Created:Created, @cell = Created@row) < 100, "00", IF(COUNTIFS(Created:Created, @cell = Created@row) < 1000, "0"))) + COUNTIFS(Created:Created, @cell = Created@row)

APalani

Hi Paul,

The last number is repetitive.

APalani

Hi Paul,

I used the previous functions with month->day and year->month. Looks like its working.

="ECR-2021" + "-" + MONTH([Created On]@row) + "-" + DAY([Created On]@row) + "-" + IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 10, "000", IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 100, "00", IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 1000, "0"))) + COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row)))

Paul Newcome

I had initially started down that route but wanted to try to simplify it since we are now looking at the full date instead of just month and year.

But the more I look at the "simplified version" the more I realize it was flawed in a few different ways.