Help

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

Determine if a record falls between a date range?

Topic Labels: Dates & Timezones Formulas
706 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Peter_Galletta
5 - Automation Enthusiast
5 - Automation Enthusiast

I'm struggling to figure out a good way to determine if a projects start and end date span any part of a specific year where start and end dates could span multiple years.

So for example, I have records with the following columns for projects:

Project A  3/1/23 [Start Date] | 2/2/24[End Date]

Project B  10/1/22 [Start Date] | 5/1/23 [End Date]

Project C  7/1/22 [Start Date] | 3/1/24 [End Date]

Project D  1/1/24 [Start Date] | 2/1/24 [End Date]

I'm trying to determine which projects were in any part of 2023. In the example above, it would be Projects A, B and C.

Ideally I can create a formula that just outputs "2023" for any in the 2023 range.

3 Replies 3

You could create a filter where:

1. The start date is “on or after” the beginning date that you type in. 
AND
2. The end date is “on or before” the ending date that you type in.

— ScottWorld, Expert Airtable Consultant

Dan_Montoya
Community Manager
Community Manager

Screenshot 2024-04-26 at 7.57.27 PM.pngScreenshot 2024-04-26 at 7.57.21 PM.png

this formula determines which year the "entered service" occurred in.  You could do the same for the end date,  then if either of those are 2023, you have your answer.

AlliAlosa
10 - Mercury
10 - Mercury

This would be more difficult (if not impossible) if you needed to get a range of years, but if you're just looking to check if something occurred within 2023 specifically, here's what I would do.

IF(
  AND(
    {Start Date},
    {End Date}
  ),
  IF(
    AND(
      YEAR({Start Date}) <= 2023,
      YEAR({End Date}) >= 2023
    ),
    "Occurred within 2023"
  )
)