My formula has expanded to display duration in dd:hh:mm:ss. And it looks like things are closer.
Open date/time=5/23/12 8:21:21
Target date/time=5/24/2012 8:01:00
WhilePrintingRecords;
NumberVar TotalSec := DateDiff ('s',{Incident.Open Date & Time},{Incident.TargetDate});
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;
Totext ( Days , '##' ) + ':' +
Totext ( Hours , '00' ) + ':' +
Totext ( Minutes , '00' ) + ':' +
Totext ( Seconds , '00' )
Returns a duration of 23:29:23, which I believe to be pretty accurate.
But when I try to add a weekend and holiday exclusion for the same Open and Target dates
WhilePrintingRecords;
Local DateVar Start := date({Incident.Open Date & Time});
NumberVar TotalSec := DateDiff ('s',{Incident.Open Date & Time},{Incident.TargetDate});
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;
Local DateVar End := date({Incident.TargetDate});
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;
Weeks:= (Truncate (End - dayofWeek(End)+1
- (Start - dayofWeek(Start)+1)) /7 ) * 4;
Days := DayOfWeek(End) - DayOfWeek(Start) +1+
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );
Weeks + Days - Hol;
Totext ( Days , '##' ) + ':' +
Totext ( Hours , '00' ) + ':' +
Totext ( Minutes , '00' ) + ':' +
Totext ( Seconds , '00' ) ;
Which returns a duration of 2:23:29:28. From reading other posts, the "weekend exclusion" part apparently is counting days differently, although I'm not sure I understand where. I have another calcuation in which the duration is actually about 10 days, but show up in the "weekend exclusion" as
-2:21:29:48.
I'm pleased that my hours, minutes, and seconds remain consistant, but just don't have the experience to deal with that "weekend exclusion" element.
Once again, we are using version 10.
Edited by sillslv - 24 May 2012 at 5:57am
IP Logged