need help writing an 'aging' formula

[PolkManiac]

Here's what I'm trying to do. I have a day that a task was created, we'll call it 'Created_Date'. What I want to do is basically return all the records where the created date is more than 7 days old. This part is easy enough with something like

{%Date} > {Created_Date} + 7

where %Date is an SQL expression returning the current date.

The tricky part is excluding weekends from this calculation - I want all the records older than 7 days, but only 7 business days, excluding weekends (don't care about Holidays).

Is there an easy way to do this? I tried a formula, referenced in the link below - but that doesn't seem to be the right way to do it.

http://www.tek-tips.com/viewthread.cfm?qid=1196009

Any help or hints would be greatly appreciated!

Thanks

 

[PolkManiac]

Ok, here's another way I'm trying to do this...with a loop

I'm create a DateVar 'Created' and setting it initially equal to the 'Created_Date' from the table. I'm also createing a NumberVar that's basically serving as a counter for the age.

The loop basically checks the Created value and if it's a weekday, increments the Age variable by 1 and if a weekend doesn't touch the Age variable. And regardless if a weekend or weekday, the Created variable is also incremented by 1 - the loop runs until the Created variable is equal to the current date.

Global DateVar Created = {V_BAR_SR.CREATED};
Global NumberVar Age = 0;

While Created < CurrentDate
Do
(
If DayOfWeek (Created) <> 1 or DayOfWeek (Created) <> 7 Then
    Age = Age + 1;
    Created = Created + 1;
Else
    Created = Created + 1;
)

The problem is that I can't get it to run. I get an error message telling me that the ) is missing before the else statement. I've tried multiple variations, and can't seem to get anything to work.

 

[lbass]

Try a record selection formula like:

{table.createddate} < currentdate -
(7 + datediff("ww",currentdate-7,currentdate,crSaturday) +
datediff("ww",currentdate-7,currentdate,crSunday))-1

-LB