Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

'If today is within (date range)' formula

326 3
cancel
Showing results for 
Search instead for 
Did you mean: 
graceallen
4 - Data Explorer
4 - Data Explorer

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! 

3 Replies 3
Mike_AutomaticN
7 - App Architect
7 - App Architect

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

TheTimeSavingCo
18 - Pluto
18 - Pluto

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

Does this look right?

Screenshot 2024-09-25 at 6.12.16 PM.png

 

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

graceallen
4 - Data Explorer
4 - Data Explorer

Thank you both! Huge help!