May 21, 2019 01:22 PM
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
So i need to filter (show matches 1,2,3,4) and count this matches.
Any clues ? Thanks in advance.
May 21, 2019 10:00 PM
Can you simply create a filter with 2 criteria?
May 22, 2019 05:36 AM
Aside from what @ScottWorld suggested, would the new Gantt block be of any use for this appplication?
May 22, 2019 08:18 AM
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.
May 22, 2019 08:20 AM
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).
May 22, 2019 08:58 AM
My guess is that you can probably create a formula (or multiple formulas) using the
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).
May 22, 2019 10:16 AM
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.
May 22, 2019 10:34 AM
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.
May 22, 2019 10:36 AM
I totally understand and intend to keep using Airtable. Nice trick. I´ll try this !