Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need help writing an 'aging' formula

Status
Not open for further replies.
Feb 23, 2006
8
US
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
Code:
{%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.


Any help or hints would be greatly appreciated!

Thanks
 
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.

Code:
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.
 
Try a record selection formula like:

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

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top