Auto Number to Reset Daily (PO #)

MCorbin

I'm trying to create a PO #. They want to include the date and a sequential number in the PO #, e.g.

2020031100005

2020031100006

I can use the "Auto-Number" function, BUT... they want the number to reset back to 1 for the next date... which auto-number doesn't do.

Is there a way to do a running count that would basically add a number for each row added on a given day?

Paul Newcome

=COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)

Should not give a sequential date but a sequential number based on the date.

In all reality... Any formula can cause performance issues on larger sheets. A lot of it depends on what else you also have going on. I have one sheet that is only about 700 rows long. There are a handful of basic formulas in every row (to include a variation of the above). That doesn't slow down too much. It's when I add in the additional 20x23 section of wicked complex formulas that it runs REALLY slow. It can take upwards of 10-15 minutes for the sheet to process something as simple as the addition of a finish date. Ugh! I too have reached out to Smartsheet and gotten the same reply that they are working on the performance issues. Hopefully they are able to upgrade sooner rather than later.

Andrée Starå

Hi,

As a possible workaround, the third-party service, Zapier, could be an excellent option for this scenario.

Is that an option for you?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

MCorbin

Unfortunately, Zapier isn't an option for us at this time. I did find a way to make it work - it's not ideal, but it works.

I have a header row where this column ("Reset PO") = 0

=IF([PO Date]@row = [PO Date]5, [reset PO]5 + 1, 1)

It's looking at the PO Date in the previous row. If it's the same date as the current row, it adds 1 to the the Reset PO # from the previous row. If the current row is a different date, it starts with 1.

Paul Newcome

There is another option...

=COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)

This will give you the sequence for the count for the date.

=YEAR([PO Date]@row) + "" + MONTH([PO Date]@row) + "" + DAY([PO Date]@row)

This will output the date as yyyymmdd

Now we use an IF statement to insert the leading zeros before the COUNTIFS to make then all the same length (00001 - 99999 kind of thing).

=IF(COUNTIFS(.............) < 10, "0000", IF(COUNTIFS(............) < 100, "000", IF(COUNTIFS(..........) < 1000, "00", IF(COUNTIFS(............) < 10000, "0")))) + COUNTIFS(............)

We drop the COUNTIFS in the above to give your zero filled count:

=IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10, "0000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 100, "000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 1000, "00", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10000, "0")))) + COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)

Finally we drop the date portion of the sequence into the beginning, and you should have the numbers you are looking for...

=YEAR([PO Date]@row) + "" + MONTH([PO Date]@row) + "" + DAY([PO Date]@row) + IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10, "0000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 100, "000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 1000, "00", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10000, "0")))) + COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)

MCorbin

Thanks - that would work....

=COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) would give a sequential date, but I've used that formula (or one similar) before for other things and have had issues with performance getting bogged down when my sheets get large (with that said, it's something Smartsheet is working on).

Paul Newcome

=COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)

Should not give a sequential date but a sequential number based on the date.

In all reality... Any formula can cause performance issues on larger sheets. A lot of it depends on what else you also have going on. I have one sheet that is only about 700 rows long. There are a handful of basic formulas in every row (to include a variation of the above). That doesn't slow down too much. It's when I add in the additional 20x23 section of wicked complex formulas that it runs REALLY slow. It can take upwards of 10-15 minutes for the sheet to process something as simple as the addition of a finish date. Ugh! I too have reached out to Smartsheet and gotten the same reply that they are working on the performance issues. Hopefully they are able to upgrade sooner rather than later.

MCorbin

Oops - that was a typo - number, not date.

My formulas must not be too complex - so far this one (or the one that was similar) was the only one that gave me a performance issue.

Paul Newcome

I'm surprised that shorter one gives you an issue. Either way...

It will work to produce your sequential yyyymmdd######, but with the drawback of it causing you performance issues. I can't think of any other way to accomplish what you are wanting though.

I do wonder if maybe it has to do with dates because all of my more complex formulas that cause issues are date based.

Andrée Starå

@MCorbin

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée

Pam Grant

I wanted to generate a new 4 digit number (restarting at 1000) every calendar year.

I did this by creating 4 dummy rows at the start of my sheet. (I don't know why it took 4 dummy rows to continue the formulas with each new entry but it worked.)

The Year Created column formula is =YEAR(Created@row).

Row 1 4 digits formula is hardcoded at 996.

Row 2 formula is =IF([Year Created]@row = [Year Created]1, [4 digits formula]1 + 1, 1000)

I dragged this formula down to rows 3 and 4.

Users request a new number via form that is added at the bottom of the list.

My Certificate No. formula is: =[Cert # Preface]@row + " " + JOIN(YY@row:[4 digits formula]@row)

You could "suck" the date out of the Created auto column to do your comparison.

Paul Newcome

@Pam Grant I usually use a single formula with a helper column.

I insert the auto-number column with no special formatting then use what basically amounts to a COUNTIFS to get my count for how many previous entries there were for the year in the Created column. A nested IF evaluating that COUNTIFS takes care of the zero fill, but in your case since you are starting at 1000, you would just need to add the COUNTIFS to 999 (so that the first entry for the year is 1000).

="CSANe" + (999 + COUNTIFS(Created:Created, YEAR(DATEONLY(@cell)) = YEAR(DATEONLY(Created@row)), Auto:Auto, @cell <= Auto@row))