Reset sequence of number back to 1, based on date change.

I am using a column called Date, formatted as Date. My second column is called Row ID which is my Sequence column, and it is formatted as AutoNumber starting at 1. When the user fills out the Form Date and submits the form, I want the sequence number to increase the RowID By 1, starting at 1 first thing in the morning.

Tomorrow when the date changes, I want the first Row ID to start at 1 again and repeat sequence for that day. I tried mirroring some other Countifs functions, but I am lost..

What would be the best way to accomplish this?

Find more posts tagged with

Accepted answers

TVang

Hi, @GRoush_CHC , here's one approach.

Ensure that your auto-number column has no prefix nor suffix (so that it remains a number instead of a string/text), and rename it.

Create a new "Sequence" column and place the column formula:
=COUNTIFS(Date:Date, =Date@row, AutoNum:AutoNum, <=AutoNum@row)

Hope this helped!

All comments

Courtney S.

Every row in an Auto-Number column is meant to be unique, so it's not going to let you set up a situation where you get multiple rows numbered with the same number the way you want. However, you could make an auto-number column that does include the date as part of a prefix or suffix in the auto number column - see this article:

Auto-number rows | Smartsheet Learning Center

TVang

Hi, @GRoush_CHC , here's one approach.

Ensure that your auto-number column has no prefix nor suffix (so that it remains a number instead of a string/text), and rename it.

Create a new "Sequence" column and place the column formula:
=COUNTIFS(Date:Date, =Date@row, AutoNum:AutoNum, <=AutoNum@row)

Hope this helped!

GRoush_CHC

Courtney, thanks for the reply, but using the route Toufong mentioned is working perfectly. I was even able to add in other criteria to count on which makes it even better. We are using the following Fx.

=COUNTIFS(Date:Date, =Date@row, ActionNum:ActionNum, <=ActionNum@row, Autoclave:Autoclave, =Autoclave@row )

Courtney S.

Nice! I'm glad Toufong's solution worked so well for you!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.