Help with reporting between 2 dates

Hi,

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.

Find more posts tagged with

Formulas

Countif

Accepted answers

All comments

heyjay

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

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

Is that your goal?

NealT

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

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.

NealT

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.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.