INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formula Help

How to calculate the amount of time of a problem minus holidays, weekends and working hours by paverley
Posted: 16 Sep 04 (Edited 16 Sep 04)

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[i] 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



Back to Business Objects: Crystal Reports 1 Formulas FAQ Index
Back to Business Objects: Crystal Reports 1 Formulas Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close