Help

Date formula or new field?

Topic Labels: Formulas
Solved
Jump to Solution
784 5
cancel
Showing results for 
Search instead for 
Did you mean: 
grecol
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
grecol
5 - Automation Enthusiast
5 - Automation Enthusiast

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

 

See Solution in Thread

5 Replies 5

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:
yearActive.png

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?

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

 

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