Formula for the Date Column to show the past 30 days

Options
Angela L
Angela L ✭✭
edited 06/15/23 in Formulas and Functions

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.


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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)))

  • Angela L
    Options

    @Paul Newcome

    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?



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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)

  • Angela L
    Options

    @Paul Newcome

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!