Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Count if date is last 7, 30, X days

Leah
Leah
edited 12/09/19 in Archived 2017 Posts

I have a sheet that lists feature requests. The system automatically generates a date when the request was submitted. I have summary columns up top of how many features were requested Q1-Q4 but I would like to add in counts for the past 7 days and 30 days. I would like a formula so the counts automatically update without myself having to adjust constantly. I have not been able to figure this out.  The formula I used for the quarterly counts were like this:

=COUNTIF([Date Requested]:[Date Requested], AND(@cell >= DATE(2017, 7, 1), @cell < DATE(2017, 10, 1)))

Any help would be greatly appreciated. 

Tags:

Comments

  • Robert S.
    Robert S. Employee

    Hello,

     

    The formula can be written similarly to the one you've already created, but using the TODAY() function to keep it up to date. This is what I came up with:

    Last 7 Days

    =COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-7)))

     

    Last 30 Days

    =COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-30)))

     

    The TODAY() function would allow this to change every day that you look at this. The negative number in the TODAY() function's parentheses can be set to any amount of days.

     

  • Hi Leah,

    You might consider using the TODAY() function in your formula, for example:

    =COUNTIF([Date Requested]:[Date Requested], <TODAY(-30))

    Instead of using COUNTIF(AND()), you can use COUNTIFS:

    =COUNTIFS([Date Requested]:[Date Requested], <TODAY(-30), [Date Requested]:[Date Requested], >TODAY(-7))

    More on these functions in our functions list: https://help.smartsheet.com/functions

  • nfatolitis
    nfatolitis ✭✭✭

    I had the same question.  Thanks. smiley

This discussion has been closed.