Calc Field to Find Day 100 in a Range

[jcohen]

I would like to create a calculated field that can give me the date for day 10 searching through the related records.

Example:

Jon Doe has worked for 20 days. There are several records that have a start date and a stope date. Let's say that record one has a start date of 1/1/02 and a stop date of 1/5/02. This gives me 5 days worked. The next record has a start date of 3/1/02 and a stop date of 3/15/02 giving me 15 days worked for a grand total of 20 days worked. The calculated field would need to return a date of 3/5/02; day number 10 of the 20 days worked.

Any help would be appreciated.

[RussBaker]

A concept answer - no script just yet (and this just addresses all days - not Mon-Fri working days only) ...

create a field for days worked this period

create a field for cumulative days worked

create a global field to store the number you want to search for ( I'll use 10 in this example)

Find all the records for john doe and sort them by start date - oldest first.

Run a looping script through these records which:

calculates the cumulative days worked at the end of each period (record).

checks to see if the cumulative days worked is >= 10. If not - on to next record in the loop.

If it is, then the date you are after is:

start date for this record + days worked this period - (cumulative days this record - 10 + 1)

There may be a more compact way to do this using a running count summary field for cumulative days worked.

Russ Baker

Canberra, Australia