Counting date ranges that meet a specific condition, but ignore any dates that fall within the "current" range

[Aurora]

It is late so I apologize but hopefully I’m explaining myself clearly.

I am trying to figure out what formula I can use to count dates, but without any of the dates overlapping. In essence, only count succeeding dates after the first date range has ended. The ideal result, it solely targeting the birds, would be 3.

For example, in the table below the succeeding dates marked in yellow are A, D, E, G, H, I. Although only A, E, and G have the condition I’m looking for. The selected date range should update every time a new range is referenced.

In the event that it matters, I do plan to also have a separate Time based start date. If it doesn’t impact the formula suggested I’m sure I can figure out that aspect, I don’t want to be a bother. Hopefully this is doable, google searching for a few hours hasn’t landed me any luck. Thank you for your time.

[Lupp]

From a pinned thread:

@Lupp

3. However, an image should only be attached if the question is about an issue of the view. (Generally the value of images is gravely overestimated nowadays.)

This is obviously a case where an image is next to useless.
Attach an example file (.ods) which contains a sheet showing what you have and what you want to get.
The example should be reduced, but contain a sufficient number of (invented) data to be a reasonable background also for experiments a helper may need to see things more clearly.
Make sure to clearly distinguish between “overlap” and “fall within”.

In such a case, formatting is more of a distraction than a help.

[Aurora]

I apologize for the image provided. I was not aware that it was viewed negatively, I’ve read the forum post provided and I think I understand. I just assumed that an image was the easiest way to explain as none of the data on that image is formulas, only formatting for ease.

I will include an example file with all of the information I can muster. Ive done my best to try to explain myself but honestly if it doesent make sense or its not to your preferences just come back and let me know its not up to standards. Ill delete the post accordingly. I am not experienced with calc but doing my best, Im starting to doubt that this is possible anyway.
Idea of what im looking for.ods (21.0 KB)

I will also accept ideas that lead me in the right direction. Potential formulas and keywords I can google would help a ton.

[robleyd]

@Aurora

I just assumed that an image was the easiest way to explain as none of the data on that image is formulas

If someone tries to create a solution, it is much quicker and easier if they can use existing - your sample - data, rather than having to create a set of dummy data, or transcribe from an image.

[Lupp]

Sorry. I neither understood the explanations given by the questioner nor can I think of a realistic use case where such counting might produce a relevant result.
I also don’t understand the answer below by @koyotak.
Thus iI only show one sometimes generalizable way to count applying complicated conditions. See attachment:
disask110665_specialPeriodsCount.ods (28.1 KB)

[Aurora]

Thanks for your consideration, I am viewing the document and studying it now. I see questions asked but I don’t think you necessarily want answers so I will throw in a few clarifying points incase anyone else comes across this problem. I am well aware that I have no idea what I’m doing. I’ll leave the post open for a period but delete it if no answers solve my issue.

• A change of perspective: Imagine its a train schedule, you can only be on a single train at a time. A2 is departure and B2 is return date. If you were attempting to catch every train to “Bird” Station, you could only catch the next train after the current one has returned. Ideal result is 3 trains were taken to Bird Station, even though 4 total trains actually went to Bird station.

• Random tidbits below.

1. Do not take anything I say as important as I am merely trying to define an idea, like a blind person describing colour.

2. (“Reference” means new standard I suppose. Once you have returned from the first train on the 10th, the next earliest train to “Bird” Station is on the 15th and returns on the 20th. “Reference” is a uneducated way of saying that now, row 6 becomes the new dates you are out of town and must wait until they have concluded before boarding the next train. So on and so forth.)

3. (+1 is my belief that the formula should consider each row before the next. If possible. Better to ignore it as anyone reading this has a better understanding of how these things work)

4. (Disjoint or overlap is better understood by the above train schedule idea. Overlap means that you cant take a train that departs on the 9th if you return on the 10th), (Same idea with Intersections and full inclusions, a person can only be on one train at a time from departure to return, so in this example intersections do not apply)

5. (The monotanous nature of A is assured. They are pretty much guaranteed to be ascending in date, only B fluctuates between return dates.)

I have not had a chance to fully read Sheet 3 and understand it yet, I will edit this post when I do.

In addition, thanks for the formula Koyotak, I will research and look into that as best as I can.

[F3KTotal]

Maybe simply like in the attached file:
Idea of what im looking for.ods (34.6 KB)
in column C, 0 means overlap, 1 means no overlap
select the animal in dropdown-cell A15

[Villeroy]

qa110665.odb (60.7 KB)
Random date intervals and categories in table TBL and a sequence of unique consecutive dates in table CAL.
One query to match the categories with the unique dates, another query counting the dates by category.
The form goes backwards from left to right where you select a category, get the related dates of that category and the original table records on the right side.

[koyotak]

if (and (not (“overlap”); “bird”); /than/; /else/)