Help with reporting between 2 dates

edited 01/29/24 in Formulas and Functions


I am having issues with working out how to get a total count on dates

1st to count how many of those tasks were updated under 7 days.

=COUNTIFS({person1}, [Person1]@row, {Date updated}, <=TODAY(7))

2nd to count how many where done between 7 days and under 14 day

=COUNTIFS({Person1}, [Person1]@row, {Date updated}, >=TODAY(8), {Date updated}, <=TODAY(14))

1st count works fine but then when trying to then add a secondary criteria between 8 and 14 days it just reports 0 and then 1st count does not change.


  • heyjay
    heyjay ✭✭✭✭✭

    Assuming that the date today is 1/29, your formula is counting between >=02/06/24 and <=02/12/24.

    {Person1}, [Person1]@row,
    {Date updated}, >=TODAY(8),  --  >=2/6/24
    {Date updated}, <=TODAY(14)) --  <=2/12/24

    Is that your goal?


  • Hi Heyjay,

    So essentially what im trying to achieve is, a status field get updated with notes. This then, when updated has a trigger on the cell next to it that provides the date that it was updated on. In a seperate KPI's sheet I want to have it report if that cell was updated either in, the last 7 days, 7-14 days, 14-30 days or over 30 days.

    Assuming working off todays date that its 1/29 as the date that the date field provides then it reports back correctly for <=TODAY(). When tring to work out the 7-14 days and I amend the date field to within that range (7-14) it presents me with a 0 count rather than an incremental of 1.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can't have an updated date in the future, but your formulas are looking for future dates. You need negative numbers in the TODAY function.

    =COUNTIFS({Date}, @cell>= TODAY(-7))

    =COUNTIFS({Date}, AND(@cell>= TODAY(-14), @cell< TODAY(-7)))

    You're going the wrong way on the timeline. Think of today as zero, future dates are positive numbers, and past dates are negative numbers.

  • Hi Paul,

    I realised that after i posted my formulas above and then was not able to edit them :(. I have tried using negative's for the past dates but was not able to get any value from it. I will test using the added AND function you have advsied to see if i can get that to work correctly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!