COUNTIFS with dates

Hi all,

I'm struggling to get a formula to work that can return the number of projects that start within the next 4 weeks. In the main sheet we have a project type column and a project start date column. In the following formula Smartsheet range 1 is the project type and Smartsheet rage 2 is the start date:

=COUNTIFS({Smartsheet Range 1}, "Project type 1", {Smartsheet Range 2}, <=DATE+28)

I have also tried:

=COUNTIFS({Smartsheet Range 1}, "Project type 1", {Smartsheet Range 2}, <=(DATE+28))

I can get it to work with a set date:

=COUNTIFS({Smartsheet Range 1}, "Project type 1", {Smartsheet Range 2}, <=(DATE(2025, 3, 30)))

but I want this to work out the live number of projects so I can add a graph in a dashboard that will constantly update, so need the date to be the current date moving forwards. The above formula will only give a snapshot on that particular date.

Any help would be much appreciated.

Cheers,

Kirk

Find more posts tagged with

Accepted answers

KPH

Hi @Kirk Siddals

Instead of DATE, use the TODAY function for today's date

TODAY(28) will return today's date plus 28 days.

=COUNTIFS({Smartsheet Range 1}, "Project type 1", {Smartsheet Range 2}, <=(TODAY(28)))

For bonus points, I noticed that your formula will include all projects of the correct type that start before or on the date 4 weeks from today which will include those starting before today. If you only want those starting within the next 4 weeks you will need to add another criteria to the COUNTIFS to only include those that start today or later. That would look like this:

=COUNTIFS({Smartsheet Range 1}, "Project type 1", {Smartsheet Range 2}, <=(TODAY(28)),{Smartsheet Range 2},>=TODAY())

Hope this helps.

All comments

KPH

Hi @Kirk Siddals

Instead of DATE, use the TODAY function for today's date

TODAY(28) will return today's date plus 28 days.

=COUNTIFS({Smartsheet Range 1}, "Project type 1", {Smartsheet Range 2}, <=(TODAY(28)))

=COUNTIFS({Smartsheet Range 1}, "Project type 1", {Smartsheet Range 2}, <=(TODAY(28)),{Smartsheet Range 2},>=TODAY())

Hope this helps.

Kirk Siddals

Hi KPH,

That's brilliant, thank you so much for the answer and brilliant solution! Love the addition of the third criteria and will definitely be using it :)

KPH

Glad I could help! Thanks for the feedback.

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