Increment number if same date

Manny_S

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.

Paul Newcome

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)

Paul Newcome

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")

Manny_S

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.

Paul Newcome

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

Manny_S

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

Paul Newcome

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)

Manny_S

That worked! You, sir, are a genius!

Paul Newcome

Happy to help. 👍️