Active days during date range

December 18, 201213 yr

I looked on briandunning.com to see if there was anything that fit my needs, but I have yet to find anything.

What I'm looking for is a custom function that would return the number of days that a record has been "active" over a larger date range. I figure there would be four date inputs:

periodStartDate

periodEndDate

openDate

closedDate

Open and closed dates are the dates associated with that particular record. Closed date can also be empty, which obviously implies that it's still open. Also it appears that the closed date should not be included in the count of active days (a current issue I'm dealing with).

This is an extremely helpful calculation when it works correctly, I can easily be able to say "what cases were open on 1/1/2012" and feed that date both into the periodStart and periodEnd and get a list of results right away (records would return 1 day active during that period). Super super useful.

I'm doing something like this currently in a calculation field but I'm planning on adding this to many more tables and if I had a consistent reliable custom function it would be much easier to manage that way. My calculation is a complete mess and "hacked" over time to try and fix any issues that crop up. I was hoping there was someone here much smarter than me that is good at date math stuff.

Here's my mess of calcuation, I have fields like this all over the database and when I make a change in one I don't remember to change it elsewhere, so it's highly unreliable (and probably mostly incorrect).

Case(

(GetAsDate(placement_start_date) = GetAsDate(g_date_to)); 1;

placement_start_date <= g_date_from and IsEmpty(placement_end_date); (g_date_to - g_date_from) + 1;

placement_start_date <= g_date_from and (placement_end_date >= g_date_from and placement_end_date < g_date_to); (placement_end_date - g_date_from);

placement_start_date <= g_date_from and (placement_end_date > g_date_to); (g_date_to - g_date_from) +1;

placement_start_date <= g_date_from and (placement_end_date = g_date_to); (g_date_to - g_date_from);

(placement_start_date >= g_date_from and placement_start_date <= g_date_to) and IsEmpty(placement_end_date); (g_date_to - placement_start_date) + 1;

(placement_start_date >= g_date_from and placement_start_date <= g_date_to) and placement_end_date >= g_date_from and placement_end_date <= g_date_to; (placement_end_date - placement_start_date);

(placement_start_date >= g_date_from and placement_start_date <= g_date_to) and placement_end_date >= g_date_to; (g_date_to - placement_start_date) +1;

"")

Any help would be extremely appreciated! Surely I can't be the first person to find something like this useful!