Count with a dynamic condition on a date set in a field

Hi,
create lookup field in A from C - entry period. if I understand well, each contract has one such date.
create lookup of this field in B. Now in B you have all data to make a formula according to your condition.
IF(AND (IS_BEFORE(Date of entry into the fleet < date of entry into the period), ....  )  , 'Yes' , 'No')
note that you should compare dates with IS_BEFORE, IS_AFTER, not with < > 

Now create Count field in C, based on link to B, with built-in filter {Formula_field}='Yes'

Hello Alexey,
Thank you very much for your reply. Indeed, counting works well with your solution but for a fixed date condition. However, I want a dynamic condition from the starting table.
In other words, I'd like to create a dynamic field in the table containing the formula (in the photo, a date), which sorts on the table where the data will be retrieved. Today, you can only set a fixed date.
By the way, I use "<" and ">" by reflex, and they work well instead of IS_BEFORE, and IS_AFTER.
So today, to obtain a table with annual values, I'm obliged to make as many columns as years, which isn't very clean!

Hello everyone,
In fact there's no solution with Airtable, the function simply doesn't exist, which is what I thought.

I've gone about it in a more "agricultural" way, which isn't very clean but which works, using the only anniversary date that allows me to establish the annual periods:
- I've created as many columns as possible years in the history expected in the past: Current year, and 5 years in AR,
- For each of these columns, I've created a conditional formula for presence or absence in the park, based on the entry date and the exit date,
- From there, I include the 6 different counts (current year and 5 years in AR) in my table of contract periods,
- In other words, I have three 'populations' of insured masses: vehicles, buildings and insured equipment, which is a lot of columns per table, but also a multiple for the period table in which everything converges.

Not great, but better than nothing !

You can hack your way into the ability to set dynamic conditions on dates, but it involves several workarounds that might not be worth it.

First, you have to convert all of your dates to UNIX timestamp numbers by creating formula fields that use the DATETIME_FORMAT function. The formula would be DATETIME_FORMAT({Your Date Field} 'X')

However, because dynamic filtering doesn’t support formula fields, you would then need to create automations that automatically copy and paste your formula fields into normal number fields.

Then, you can set dynamic conditions based on those number fields.

Might not be worth it to go through all of those steps, though.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

You are right, dynamic filtering is absent for Count, Lookup, Rollup built-in filters.
Except mentioned by @ScottWorld , that might be helpful.
But in many cases dynamic value that you want to set into filter, can be obtained as Rollup in other table. Then you can add lookup of this value back in the first table and create the formula in the way you can use static values in Count, Lookup, Rollup built-in filters. Formula does dynamic part with output "Yes" or "No", and then you can count all items with "Yes".
Of course, this needs more efforts and additional columns than if built-in filters were able to add dynamic values.