Best formula field AI prompt

[grecol]

I have start dates and end dates for each project in a list of about 50 projects. I want to create a filter that will only show me records that were active between the start date and end date during specific fiscal years (where I set the FY date parameters). Some projects span multiple fiscal years, so "on or after" or "on or before" doesn't cover everything.

[Stephen_Orr1]

Hi @grecol,

To filter for projects that were active during a FY range, try this logic:

Project start date is on or before FY's end date AND Project end date is on or after FY's start date

Example for 2023:

Hope that helps!
-Stephen

[grecol]

Hmmm, thanks. That way only works for projects that don't carry across fiscal years. For example, I want to be able to know how many projects were active during any given time/date parameter. Does that make sense?

[Stephen_Orr1]

This logic supports any date range. For example,

All projects active during the start of 2023 through the end of 2025:
Project start date is on or before 12/31/2025 AND Project end date is on or after 1/1/2023

All projects active during the 2nd half of 2023:
Project start date is on or before 12/31/2023 AND Project end date is on or after 6/1/2023

All projects active during the 2nd half of 1990 through 3rd quarter of 2017:
Project start date is on or before 9/30/2017 AND Project end date is on or after 6/1/1990

Can you please provide an example where this doesn't work? 🙂

[grecol · Accepted solution]

Yes, you're right! That will work. Thank you!

[Stephen_Orr1]

Awesome! Would you be so kind as to also mark my previous answer as the solution for others to find? I had to think about this one for a little while and would really appreciate it! 🙂

-Stephen