I have a real challange and I'm Struggling...
I need to calculate the number of days within a range over a number of 'Visit Dates'. The calulation needs work out the linear change over time (in days). Here is the example to best explain what I'm talking about:-
Visit Date Value Range Low Range High
04/08/2009 1.7 2 3
18/08/2009 2.3 2 3
25/08/2009 2.6 2 3
29/09/2009 2.2 2 3
We have values from 4/8 to 29/9 ie 56 days
From 18/08 to 29/09 the values are all within the Low/High range so 42 days definitely in range
From 4/08 to 18/08 the value climbs from 1.7 to 2.3 in 14 days
So the rate in increase of the value is (assuming linearity)
is 0.6/14=0.0428571 units per day
So the value will hit 2.0 in 0.3/0.0428571 = 7 days
During these 7 days the value is between 0.7 and 2.0, therefore the value is below the target range (between 2 and 3), and for the next 7 days the value will be between 2.0 and 2.3 and is therefore in range
So overall, of the 56 days in question, 7 are below range and 49 are in range. This adds up to 87.5% of days in range.
I hope that makes sense and if anyone crack how to do it I will eternally grateful.