Skip to main content

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.

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


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.

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"
)
)

 

Reply


Terms & Conditions