nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Count the number of unique dates in the Created date column

mcullen

Hello,

I'm trying to write a Summary Field formula that will count the number of unique days in the Created date (titled Date Added) system column without using a helper column or metric sheet.

I am able to get the correct total, and the list of dates counted, when I use the Analyze data AI Tool:

However, I'm not having any luck using a formula to find the total.

Here's what I've tried (with the help of the Formulas AI Tool):

=COUNT(DISTINCT([Date Added]:[Date Added])) - returns a total of 268

=COUNT(DISTINCT(DATEONLY([Date Added]:[Date Added]))) - returns a total of 1

=COUNT(DATEONLY([Date Added]:[Date Added])) - returns a total of 1

=COUNTIF([Date Added]:[Date Added], DATEONLY(@cell) = DATEONLY([Date Added]@row)) - #UNPARSEABLE

Any help would be greatly appreciated!

Find more posts tagged with

Accepted answers

Paul Newcome

You can also do this with just one helper column where you extract just the date.

=DATEONLY([Created Date]@row)

Then the COUNT/DISTINCT should work on this new date column.

All comments

S.Stone

@mcullen I don't have a fix that works without helper columns. But when I needed to do something similar, here's the workaround I used:

Step 1: Create an Auto Number column
Step 2: Check a box for the "first instance" of each value

=[Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], Date:Date, Date@row))

Step 3: Count the number of checked boxes

=COUNTIF([First Instance]:[First Instance], 1)

mcullen

Shucks, I was really hoping I wouldn't have to add an extra column due to the volume of data that moves through the sheet.

Thank you so much @S.Stone for sharing!

Paul Newcome

You can also do this with just one helper column where you extract just the date.

=DATEONLY([Created Date]@row)

Then the COUNT/DISTINCT should work on this new date column.

mcullen

Thank you @Paul Newcome, that's the route I ended up going with.

Paul Newcome

Happy to help. 👍️

Paul Newcome

@S.Stone This is a great way to do this with a regular date column, but it may not work with the [Created (Date)] system column as that column also includes a time.

S.Stone

Thanks, Paul! I had just copy/pasted formulas from my other sheet where I had a manual date column with the checkbox structure as shown. Your single helper column is a more efficient solution for this - thanks for sharing!

Paul Newcome

@S.Stone Being able to flag the first instance is really useful too. I like your approach of a basic true/false value. I usually us an IF statement

=IF([Row ID]@row = MIN(COLLECT(……..), 1)

but your formula shows that even the IF isn't needed. Love it!

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.