Count if Between a date range

Morning Smartsheet community, Hope you all had a great festive period.

I'm struggling with a formula that would make my reports a whole lot easier…..

I have a helper column that I want to return a value@row could be anything but for ease say "1", If the date in the [Form Date]@row is between the dates set in [PP Dates]1 and [PP Dates]2

Im guessing its possible but my brain is struggling to see through the fog this morning

Thanks for any input

Find more posts tagged with

Formulas

dates

Accepted answers

All comments

Protonsponge

Hello @Bobhope84,

Could the following formula be what you are looking for?

=COUNTIFS([Form Date]:[Form Date], >=[PP Dates 1]@row, [Form Date]:[Form Date], <=[PP Dates 2]@row)

As per the demo below, the above formula will COUNTIFS the dates in [Form Date] is equal to or greater than [PP Dates 1] and equal to or less than [PP Dates 2]

You could also set this up in the sheet summary if it was more useful for your reporting as per below.

=COUNTIFS([Form Date]:[Form Date], >=[PP Dates 1]#, [Form Date]:[Form Date], <=[PP Dates 2]#)

I hope that is helpful to you in some way,

Protonsponge

Hello @Bobhope84

I just re-read your question and realised I did not answer your question, clearly I have Friday brain fog also and I have yet to have the morning coffee.

Upon re-reading your question, are you more looking for something like this?

=IF(AND([Form Date]@row >= [PP Dates 1]$1, [Form Date]@row <= [PP Dates 2]$1), 1, 0)

Sorry for the initial mis-understanding and I hope something in the above posts is helpful to you… off for that coffee now :-)

Protonsponge

Bobhope84

https://community.smartsheet.com/discussion/comment/467803#Comment_467803

Thanks for the reply.

So Kinda, But not quite…..

This formula will count the total entries with the date range between the specified dates

=COUNTIFS([Form Date]:[Form Date], >=[PP Dates]1, [Form Date]:[Form Date], <=[PP Dates]2)

The bit I'm struggling with is I'd like it to return something in the helper column @row so I can filter/Report using the column

I have 30 reports that I need to change the value of the filter dates each month so my plan is i change the filter to look at the helper instead of the column and I can change one date range on the sheet ([PP dates]1 & pp Dates]2) and then all the reports are updated to reflect the new dates…. if that makes sense?

Protonsponge

@Bobhope84 - really sorry for mis-reading your initial Q, was the 2nd post what you were looking for?

Protonsponge

Bobhope84

https://community.smartsheet.com/discussion/comment/467805#Comment_467805

=IF(AND([Form Date]@row >= [PP Dates]1, [Form Date]@row <= [PP Dates]2), 1, 0)

So this is the formula that worked, Thanks for your help.

Hope you enjoy your morning coffee, You've earned it

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.