I thought I'd second the notion because
I have a table that contains multiple (upwards of 30) Start/End Dates for trips and a calculation
raises two red flags:
- you are designing tables like spreadsheets (wide) instead of 'tall' as they should be
- you have calculated fields in tables (not good)
Don't put the cart before the horse - i.e. make sure you understand database design principles & naming conventions and be sure you are aware of the pitfalls (calculated fields in tables, lookup fields in tables, etc.).
In the end, you'll probably need a custom function to pull out the part of a phase (e.g. trip) that begins in one period but ends in another. This function might make use of the DateDiff function, where you calculate the difference between a start date and the end of a period rather than using the end date of the phase. Conversely, you'd probably need to do the reverse if you want to capture the remainder in a search that begins in the next period
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.