NEED AGING FORMULA THAT DOES NOT COUNT THE WEEKENDS (modified request) 2

[Nmartin95]

How can I get an aging formula which does not take into consideration the weekend? So it would be how ever long a work order was open not counting the weekends into the age.

Note: There is no end date...until a work order is in "close" status. So the only field from the database that can be used in a formula is the work order open date. I am only dealing with "open" work orders.

This is what I am using now to get the age:

IF {BSCC-FL-TM&ACTION_JOIN.Call_Status} = 'PENDING' THEN
{BSCC-FL-TM&ACTION_JOIN.Status-History.PENDING.TIME} - {BSCC-FL-TM&ACTION_JOIN.Status-History.OPEN.TIME}
else

If{BSCC-FL-TM&ACTION_JOIN.Call_Status} <> &quot;CLOSED&quot; THEN
CurrentDate - {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}
ELSE
if day({BSCC-FL-TM&ACTION_JOIN.Status-History.CLOSED.TIME}) = day({BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}) then
1 //instead of displaying &quot;0&quot;
else
{BSCC-FL-TM&ACTION_JOIN.Status-History.CLOSED.TIME} - {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}

I tried using Ken. H's website formulas, but the only problem is I would need a end date for it to work. Thanks for suggestion though.


Any help would be greatly appreciated thanks. I am using crystal reports 7.01.

 

[Nmartin95]

Sorry guys, I gave you the wrong formula, this is what I am using:

IF {BSCC-FL-TM&ACTION_JOIN.Call_Status} = 'PENDING' THEN
{BSCC-FL-TM&ACTION_JOIN.Status-History.PENDING.TIME} - {BSCC-FL-TM&ACTION_JOIN.Status-History.OPEN.TIME}
else
If (Today - {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}) > 7 then
(Today - {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}) -
Truncate ((Today - {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received})/7)*2
else
(Today - {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}) - Truncate
((Today - {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}+
DayOfWeek ({BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received}))/7)*2

 

[Naith]

I don't know if you're already aware of this, but you can download a business days zip from the Crystal site for this.

On the other hand, your formula does use two dates:

'Today' (or 'currentdate') being the end date, and
{BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received} being the start date.

This being the case, this formula from Ken's site should work.

WhilePrintingRecords;
DateVar Start := {table.StartDate}; //Replace this field with your Starting Date field
DateVar End := {table.EndDate}; //Replace this field with your Ending Date field
NumberVar Weeks;
NumberVar Days;
NumberVar Hol:= 0;

//Figure the number of Calendar &quot;Rows&quot; involved and count 5 days for each:
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

//Adjust the first and last weeks based on when in the week you start and end
Days := DayOfWeek(End) - DayOfWeek(Start) + 1
+ (if DayOfWeek(Start) = 1 then -1 else 0) //adjust for starting on a Sunday:
+ (if DayOfWeek(End) = 7 then -1 else 0); //adjust for ending on a Saturday:

//Adjust for Holidays in the period between the start and end dates:
if Date(1999,01,01) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,01,18) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,02,16) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,05,31) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,09,06) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,11,25) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,12,24) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,03) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,17) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,02,14) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,05,29) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,09,04) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,11,23) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,12,25) in start to end then Hol:= Hol+1 else Hol:= Hol;

//Assemble the adjusted work days
Weeks + Days - Hol

Naith

 

[Nmartin95]

Where can I find the business days zip? What is the name of the website.

 

[feazelb]

In CR8 I use the following to obtain the age without counting weekends:

datediff('d', {firstdate}, {seconddate})-
datediff('ww', {firstdate}, {seconddate},crSaturday)-
datediff('ww', {firstdate}, {seconddate},crSunday)

Don't know if this works in CR7 but hope it helps.

feazelb

 

[Naith]

http://support.crystaldecisions.com/docs

cr_business_days_hours.zip

Naith