Auto sequence based on date

Chris Lowry

I am looking for a formula to sequence or auto number rows based on a date and order the sequence number simply 1 through infinity. I would like this formula to work even when I add or remove rows or change the dates. I also can't have any duplicate numbers in the Rankeq formula and has a tie breaker column.

jmyzk_cloudsmart_jp

Hi @Chris Lowry

To generate a unique sequence number based on a date column — even when multiple rows share the same date—use the following setup. This approach ensures stable, gapless numbering that automatically updates when rows are added, removed, or modified.

https://app.smartsheet.com/b/publish?EQBCT=b745805fde464207803c053116a4425a

Required Columns:

• Date Number – Numeric conversion of the date
• RANKEQ – Rank of the date
• Tie Breaker – Resolves duplicate ranks
• Sequential number – Final unique sequence number
• (Optional) Row ID – A unique value to restore input order (can use Auto Number)

Sheet Summary Field(Optional):

• [MIN DATE] =MIN(Date:Date)

Column Formulas:

• [Date Number] =Date@row - [MIN Date]# + 1
• [RANKEQ] =RANKEQ([Date Number]@row, [Date Number]:[Date Number], 1)
• [Tie Breaker] =MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], RANKEQ:RANKEQ, RANKEQ@row), 0) - 1
• [Sequential number] =RANKEQ@row + [Tie Breaker]@row

Notes:

The Row ID column is optional. It can help preserve the original row input or creation order during tie-breaking but is not required for basic functionality.

The [MIN DATE] sheet summary field is optional. For example, you can replace it with DATE(2023, 1, 1).

jmyzk_cloudsmart_jp

Hi @Chris Lowry

To generate a unique sequence number based on a date column — even when multiple rows share the same date—use the following setup. This approach ensures stable, gapless numbering that automatically updates when rows are added, removed, or modified.

https://app.smartsheet.com/b/publish?EQBCT=b745805fde464207803c053116a4425a

Required Columns:

• Date Number – Numeric conversion of the date
• RANKEQ – Rank of the date
• Tie Breaker – Resolves duplicate ranks
• Sequential number – Final unique sequence number
• (Optional) Row ID – A unique value to restore input order (can use Auto Number)

Sheet Summary Field(Optional):

• [MIN DATE] =MIN(Date:Date)

Column Formulas:

• [Date Number] =Date@row - [MIN Date]# + 1
• [RANKEQ] =RANKEQ([Date Number]@row, [Date Number]:[Date Number], 1)
• [Tie Breaker] =MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], RANKEQ:RANKEQ, RANKEQ@row), 0) - 1
• [Sequential number] =RANKEQ@row + [Tie Breaker]@row

Notes:

The Row ID column is optional. It can help preserve the original row input or creation order during tie-breaking but is not required for basic functionality.

The [MIN DATE] sheet summary field is optional. For example, you can replace it with DATE(2023, 1, 1).