
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 26, 2024 02:56 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 26, 2024 04:12 PM
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 26, 2024 07:59 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2024 08:56 AM
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"
)
)
