# Formula for the Date Column to show the past 30 days

Options
✭✭
edited 06/15/23

I am trying to write a formula where I can flag the columns that have dates within the past 30 days.

Once I do that, then I would like to count the duplicate Article Names to determine how many views the column has had in the past 30 days.

• ✭✭✭✭✭✭
Options

To flag rows that were viewed within the past 30 days you would use:

=IF(AND([Article Date View]@row>= TODAY(30), [Article Date View]@row<= TODAY()), 1)

Then in another column (called "Dupe" in this example) you would use:

=IF(COUNTIFS([Article Name]:[Article Name], @cell = [Article Name]@row, [Article Viewed in The Past 30 Days]:[Article Viewed in The Past 30 Days], @cell = 1)> 1, 1)

Then to count how many were viewed more than once in the past 30 days, you would use

=COUNT(DISTINCT(COLLECT([Article Name]:[Article Name], Dupe:Dupe, @cell = 1)))

• ✭✭
Options

Thank you so much for your response!

When I am inputting the formula for the first one to flag the past 30 days, I am not getting any update into the cell, everything is blank. I have the formula copied all the way down and every row is blank.

Any ideas?

• ✭✭✭✭✭✭
Options

My apologies. I missed the - in the first TODAY function.

=IF(AND([Article Date View]@row>= TODAY(-30), [Article Date View]@row<= TODAY()), 1)

• ✭✭
Options

Sorry to keep bugging you. Would I even need to track the duplicates? Since every line has a duplicate, they would all be "1".

I also can't get the =COUNT(DISTINCT(COLLECT([Article Name]:[Article Name], Dupe:Dupe, @cell = 1))) to work correctly. I am looking for it to total the 30 day views for each Article Name.

• ✭✭✭✭✭✭
Options

That is flagging duplicates within the past 30 days.

If you want the total count within the past 30 days for each article, then you would use this instead:

=COUNTIFS([Article Name]:[Article Name], @cell = [Article Name]@row, [Article Date View]:[Article Date View], AND(@cell>= TODAY(-30), @cell<= TODAY()))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!