count no of occurrences from a time interval

[Dinesh_Excel]

Hello Friends,

I need your help in getting the no of occurrences for a time interval from a range of dates.

for example in column A we have time intervals for 15 days between the dates and from column G we have planned dates , now in column B we need to count the number of occurrences that planned dates in column B.

time interval between 12/30/2018 - 1/31/2019 , we have 2 occurrences in planned dates i.e 11/12/2018 and 1/23/2019 and so on..

Appreciate your help here.

 

[vletm]

Dinesh_Excel
time interval between 12/30/2018 - 1/31/2019 ,
we have 2 occurrences in planned dates i.e 11/12/2018 and 1/23/2019 and so on..
Wasn't 11/12/2018 before 12/30/2018?
(... if 11 was November and 12 was December)

You could test to Cell B2
=COUNTIFS(G:G,">=" &A$2,G:G,"<=" &A2)
and copy down

 

[Dinesh_Excel]

thanks Vltem..but I guess I was not able to convey the right message to you.

let me try again :

I have dates with dates having time intervals in column A , and planned dates in column G.
lets say when between 12/30/2018 the planned dates has only 11/12/2018 hence we will count it as 1
again between 12/30/2018 and 1/15/2019 we have only one date in "Planned dates column, hence the count remains as 1
now from 12/30/2018 to 1/31/2018 when we check the planned dates , we have 2 as count .

hope this clarifies.

Time interval	Occurennce from colum G
12/30/2018​	1​
1/15/2019​	1​
1/31/2019​	2​

Planned Dates

11/12/2018​

1/23/2019​

 

[vletm]

Dinesh_Excel
You mean ... let say ... do You mean
that between 30-Dec-2018 and 15-Jan-2019 ... is hmm?
You've named to possible ... 12-Nov-2018 or 23-Jan-2019 .
Isn't 12-Nov-2018 BEFORE 30-Dec-2018 or how?
Isn't 23-Jan-2019 AFTER 15-Jan-2019 or how?
Between 30-Dec-2018 and 31-Jan-2019 ... yes ... there is ONE planned date; 23-Jan-2019.
Which calendar system do You use?

 

[Peter Bartholomew]

Things you did and did not wish to know about planned events:



= COUNTIFS(Planned[Date], "<" & [@Date], Planned[Date], ">" & intervalStart)
= COUNTIF(Planned[Date], "<"&[@Date])
= TEXTJOIN( ",", 1, IF( (Planned[Date] >= intervalStart) * (Planned[Date]<[@Date]), Planned[ID], "" ) )
where 'intervalStart' is a named formula