Skip to main content

Hi, 

I'm trying (and struggling) to create a formula that allows us to filter by 'people who are currently contracted by us'. We have a start date and end date column within the table, so ideally I'd like to add a formula that allows us to filter based on whether today is in between their start and end date. Does anyone know if this is possible please? Or if there's an easier method of filtering that doesn't involve us manually ticking a box for people that are currently contracted. 

Thanks! 

Hey @graceallen!

This can be solved by doing the following:

1. Create a new formula field called something like "Currently Active"
2. Use the formula below assuming you have Start Date and End Date fields:

AND({Start Date} <= TODAY(), OR({End Date} = BLANK(), {End Date} >= TODAY()))

3. The above formula will return 1 if it is an active employee, and 0 if it is not active employee.
4. Create a view which will with the Filter "Currently Active = 1".

Hope this helps!

Mike, Consultant @ Automatic Nation

Edit: Whoops, just use Mike's formula above, it's a lot more elegant than mine!

Does this look right?

 

AND(
OR(
IS_SAME(
TODAY(),
{Start Date},
'days'
),
IS_BEFORE(
{Start Date},
TODAY()
)
),
OR(
IS_SAME(
TODAY(),
{End Date},
'days'
),
IS_AFTER(
{End Date},
TODAY()
)
)
)

 

Link to base

The main caveat is that your date fields need to be set to GMT as that's how the formulas calculate the time, and so the time must be displayed.  I think we might be able to make the formula take your current timezone into account but that would take a lot of experimentation

You could also try using the filters instead as those take your timezone into account, and, assuming you haven't manually set a timezone for your date fields, then it should work automatically

Thank you both! Huge help! 

Reply


Terms & Conditions