Increment number if same date

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.

Find more posts tagged with

Accepted answers

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)

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.