Skip to main content

Context:  I'm working on an Animated TV Show and have made a Base for tracking the Production.  The main function so far has been tracking all of the various Meetings/Milestones/Working Periods that happen within a certain Department.  I also have a Crew List Table and another Table that tags those Crew Members to specific Teams all within the same base.  I then have tagged those Teams to the Events that they're needed for, so I could add those people to Google Calendar for Meetings, but also to track what Teams are working on at any given time and what Milestones they have to be aware of.  One feature I'm trying to build is a way to quickly evaluate Time Off requests by plugging in their requested start/end for their Holiday and then seeing all of the Events that fall within that range - see screenshot for the filters I started with.

Problem:  I had a Working Period in this instance (see screenshot), that the Team shown needs to be involved in, that wasn't captured because the Start Date for it was Before the First Day AND the End Date was After the Last Day of their requested time off.  Basically their whole Time Off request falls within the larger Date Range of the Working Period.

How do I rework this filter to also capture these records that involve work within the requested Date Range?

Hello ! I wouldn't use a filter but rather a formula field that would flag the record. 
Something like : if ( and ( or ( start date < xx/xx, star date < xx /xx, or ( end date < xx / xx, end date < xx / xx )), 1)

Then you would be able to use a filter to show only the flagged records 

Does it make sense for you ?

Hello ! I wouldn't use a filter but rather a formula field that would flag the record. 
Something like : if ( and ( or ( start date < xx/xx, star date < xx /xx, or ( end date < xx / xx, end date < xx / xx )), 1)

Then you would be able to use a filter to show only the flagged records 

Does it make sense for you ?


Well I think that formula still wouldn't catch the instances I'm after, although I see your point.  Also, that means I have to go in and edit a formula every time for this, though I guess that would be the best way to eventually automate this, which is my goal 🤔 🤔 I could make Leave Requests a form and then that could insert the Dates into the formula for me each time, which would then flag it.  If you can help me with the date falling within a range issue of this, this might have solved 2 problems!

Reply


Terms & Conditions