Sequential Number
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:
Answers
-
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)
-
great Thx, will try this.
-
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?
-
This is what i tried but keep getting same value for the month. Is there any better solution?
-
@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)))
-
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!
-
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)))
-
Thank you so much Paul, the function works well.
-
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.
-
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)
-
Hi Paul,
The last number is repetitive.
-
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)))
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!
