Counting number of events (lines) within a time range

Alex Brioso

Hi,

I looked at the past discussion but didn't find the answer.

My sheet has a row for each events happening with the precise dates (ex: 8-Aug-2024). I would like to count any event that happened within a time frame (march 1rst to march 31rst of the next year).

I tried various formulas but no luck at all…

NickStaffordPM

Make a column to the right of Date Realisee column, use the following formula

=iferror(if(AND([Date prevue]@row>=DATE(2025,03,01),[Date realisee]@row<=DATE(2025,03,31),1,0),0)

Make this formula the column formula for this new column

In your Sheet Summary, create a metric and use the following formula

=COUNTIF([New Column To The Right Of Date realisee]:[New Column To The Right Of Date realisee],1)

You can then make one metric for every month of the next year by changing the values within the DATE functions.

Let me know if this did not help you or if I misunderstood.

RDRGSJ00

Salut Alex Brioso,

I came up with the below solution, I hope it helps:

I have a mock sheet with the below data and columns:

I came up with two options:
Option one: is the [All Activities] column where I have the below formula:

=IFERROR(JOIN(COLLECT([Theme Activity]:[Theme Activity], [Expected Date]:[Expected Date], >=[Start Date]@row, [Expected Date]:[Expected Date], <=[End Date]@row), ","), "--")
This formula will JOIN all of the activities within the [Start Date] and [End Date] columns separated by a ","

Option Two: is the [Count of Activites] column where I have the below formula:

=IFERROR(COUNTIFS([Expected Date]:[Expected Date], >=[Start Date]@row, [Expected Date]:[Expected Date], <=[End Date]@row), "--")
This formula will count the number of activities that are within the [Start Date] and [End Date] column