# Increment number if same date

Options

Hello. I am using a formula to remove the slashes from a date. I would like to use the resulting value as the row's unique ID, by adding an auto incrementing 2-digit suffix.

Here is the formula:

=YEAR([Date]123) + "" + RIGHT(100 + (MONTH([Date]123)), 2) + "" + RIGHT(100 + (DAY([Date]123)), 2) + "-01"

Which returns something like 20220127-01

Rows are automatically created via Forms. If a row is created on the same day, I want the last two digits to increment by one. Then, back to "01" when a new row is created in a different date.

Is this possible? Thanks for taking a look.

• ✭✭✭✭✭✭
Options

Try changing the 1s in the COUNTIFS functions to @cell. You also need to remove the last bit after the second COUNTIFS. The formula should end with

Numbering@row)

• ✭✭✭✭✭✭
Options

Insert an auto-number column with no special formatting. Then you can use something like this...

=YEAR(Date@row) + "" + RIGHT(100 + MONTH(Date@row), 2) + "" + RIGHT(100 + DAY(Date@row), 2) + "-" + IF(COUNTIFS(Date:Date, @cell = Date@row, Autonumber:Autonumber, @cell<= Autonumber@row)< 10, "0") + COUNTIFS(Date:Date, @cell = Date@row, Autonumber:Autonumber, @cell<= Autonumber@row)< 10, "0")

• Options

Hi Paul,

Thanks for the response. However, I keep getting #UNPARSABLE.

My Date column is the Date type and the Autonumber column is an auto-number without formatting.

I broke the formula into pieces - the first part (the dates) worked but the COUNTIFS returned #UNPARSABLE.

• ✭✭✭✭✭✭
Options

Make sure you are using the correct column name when referencing the Autonumber column. I think it defaults to [Row ID].

• Options

I changed the column name to "Numbering" and updated the formula - same result.

• ✭✭✭✭✭✭
Options

Try changing the 1s in the COUNTIFS functions to @cell. You also need to remove the last bit after the second COUNTIFS. The formula should end with

Numbering@row)

• Options

That worked! You, sir, are a genius!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!