Count if Between a date range

Bobhope84

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

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

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