Skip to main content

Count matching date ranges

  • May 21, 2019
  • 8 replies
  • 58 views
M

Hi fellows,

I am really stuck trying to solve this problem. Any help will be most appreciated.
I have several date ranges that represents employees leave/absence. In order to grant vacations i need to check how many employees are out. So I have a employee date range to be compared with several date ranges. For instance:

Range to be compared:

                   begin |-----------------| end

  1.      b |---------------------|e

  2.                                b |----------------------|e

  3.      b |---------------------------------------------|e

  4.                        b |---------|e

So i need to filter (show matches 1,2,3,4) and count this matches.

Any clues ? Thanks in advance.

8 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 22, 2019

Can you simply create a filter with 2 criteria?

  1. Begin Date is greater than or equal to (type in the begin date here)
    AND
  2. End Date is less than or equal to (type in the end date here)
Nathalie_Collin
Forum|alt.badge.img+18

Hi @Marcel_Felipe_Machad,

Aside from what @ScottWorld suggested, would the new Gantt block be of any use for this appplication?

M

Can you simply create a filter with 2 criteria?

  1. Begin Date is greater than or equal to (type in the begin date here)
    AND
  2. End Date is less than or equal to (type in the end date here)

No, because it has to match more than 2 criterias. See that the first hipothesys has to check all the beginning dates (b) that is before begin and end dates (e) after begin but also end dates (e) after begin and end dates (e) before end. And this continues to every 4 conditions.

M

Hi @Marcel_Felipe_Machad,

Aside from what @ScottWorld suggested, would the new Gantt block be of any use for this appplication?


In a certain way, but it´s a visual workaround. One had to count matching lines in a given period. This was my first workaround using spreadsheets but it became hard to work with so many records.(in one year i have approximately 700 records).

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 22, 2019

My guess is that you can probably create a formula (or multiple formulas) using the IS_AFTER and IS_BEFORE functions to compare the dates for each criteria.

And then, you can filter your records based on the result of that formula(s).

M

My guess is that you can probably create a formula (or multiple formulas) using the IS_AFTER and IS_BEFORE functions to compare the dates for each criteria.

And then, you can filter your records based on the result of that formula(s).


Do you know by chance if this functions IS_AFTER and IS_BEFORE includes the actual day? The con is that when checking multiple dates i would have to edit the formula and put the dates inside it to filter the results because the airtable doesn´t have a way to reference a constant variable in a cell just like in an ordinary spreadsheet does. If it was not for this setback the software is really amazing and very quick responsive.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 22, 2019

Right, that is another Airtable limitation for now. You can’t yet create a global/constant variable for your entire database. (Airtable is pretty powerful & amazing, but it is still in its infancy so it’s lacking a lot of features that more mature database products have.)

But what you could do is create a very simple formula field that is simply nothing more than your actual date. And then, the results of that formula (i.e. your actual date) would show up & be accessible in all of your records because the formula simply represents your actual date.

The downside to this approach is that you would need to continually edit that formula to reflect a new date, but as soon as you went into the formula to update the date, that date would show up & be accessible in all of your records.

M

Right, that is another Airtable limitation for now. You can’t yet create a global/constant variable for your entire database. (Airtable is pretty powerful & amazing, but it is still in its infancy so it’s lacking a lot of features that more mature database products have.)

But what you could do is create a very simple formula field that is simply nothing more than your actual date. And then, the results of that formula (i.e. your actual date) would show up & be accessible in all of your records because the formula simply represents your actual date.

The downside to this approach is that you would need to continually edit that formula to reflect a new date, but as soon as you went into the formula to update the date, that date would show up & be accessible in all of your records.


I totally understand and intend to keep using Airtable. Nice trick. I´ll try this !

Terms & ConditionsAccessibility statement