×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

# Contact US

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.

Students Click Here

# Business Objects: Crystal Reports 1 Formulas FAQ

 Forum Search FAQs Links MVPs

## Formula Help

 How to calculate the amount of time of a problem minus holidays, weekends and working hours by paverley faq767-5430 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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.