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.
Solved
Date formula or new field?
+5Best answer by grecol
Yes, you're right! That will work. Thank you!
+18Hi @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
+5Hi @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
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?
+18Hmmm, 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?
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? 🙂
+18Awesome! 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.