How to calculate the amount of time of a problem minus holidays, weekends and working hours

First of all, this FAQ is not made solely by myself, but it's the result of my work and work of other genius people here in this forum ;-)

It's also the result of a common question i suppose, especially for a helpdesk: calculate the amount of time between the start end the end of a problem. Off course,
weekends, non working hours and holidays need to be excluded, and that's the most difficult part of all. Finally, you need to convert the result in a common way (hh:mm:ss)

Good luck !!


//This function (@workdays) sets start and endingdates. It also substract weeks from the calculation and prepares the next function (@holidays) wich substracts also holidays from the result.

WhileReadingRecords;
Local DateTimeVar Start := {ROOTCAUSEM1.OPEN_TIME};
Local DateTimeVar End := {ROOTCAUSEM1.CLOSE_TIME};
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;

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 Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol


//Function (@holidays) which substracts holidays from your results. You can easily remove or add other holidays. Important: there is a function in CR (skipholidays) which removes holidays from your results but if you live eg. outside the usa, local holidays are not included. You have to put this field somewhere in your report header (and you can supress the header or you can colorize this field in white)

BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),

Date (2004,01,01),
Date (2004,04,12),
Date (2004,05,20),
Date (2004,05,31),
Date (2004,07,21),
Date (2004,11,01),
Date (2004,11,11),
Date (2004,04,09),
Date (2004,05,21),
Date (2004,08,16),
Date (2004,09,27),

Date (2005,03,25),
Date (2005,05,06),
Date (2005,12,26),

Date (2006,04,14),
Date (2006,05,26),
Date (2006,12,26),

Date (2007,04,06),
Date (2007,05,18),
Date (2007,12,26)
];
0



//Function actually making the calculation. It also sets the beginning and ending of your days.
WhileReadingRecords;

NumberVar Days := {@workdays};
TimeVar SetStart := TimeValue( "7:30");
TimeVar SetEnd := TimeValue("18:00");
TimeVar StartTime := TimeValue({ROOTCAUSEM1.OPEN_TIME});
TimeVar EndTime := TimeValue({ROOTCAUSEM1.CLOSE_TIME});


If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;

Numbervar dur := (Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600) )*3600; //Result is in number of seconds
//Display the results in a common format (hh:mm:ss)
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;


hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + "h" + totext(min, "00") + "m" + totext(sec, "00") + "s";

hhmmss