Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide On Location TrainingPublic ClassesIndividual TrainingIntroductory Course OutlineAdvanced Course OutlineFormula ExamplesNewsletter Back IssuesMy BlogResource LibraryConsulting ServicesSupport ServicesContact InformationLinks to third party productsBack to main pageMy Credentials
Free Crystal Reports formula examples from KenHamady.com

To learn the techniques used in these formulas get:
The Expert's Guide to Crystal Reports Formulas
and
 Expert Techniques for Crystal Reports I, II & III

PDF exPLODE

The number of "Work Hours" between two DateTime Values:

I have posted a formula that calculates the number of working days in a date range (excluding weekends and holidays).  This formula takes it one step further by allowing you to calculate the number of business hours between two date/time values.   The first step is to implement the work days formula (Formula #1).  This gives you the number of days involved.  You put the name of that formula in the second line below.  The next two lines are for you to enter the start time and end time of your business day.   This assumes that you have the TimeValue function.  Let me know if you are using an older version of Crystal, and I will give you alternative syntax.  Then the next two lines are where you give it the fields for start time and end time from your tables.   These can be true Time values, Character times or DateTime fields.  The next 4 lines are only needed if you have a string field for times, and they don't include and AM/PM indication.

One note of caution.  The formula assumes that the Start and End dates are business days,  and the Start and end times are within your working day.   You will get unpredicatble results if you have records that start and end 'after hours'.    These records need to be adjusted in a separate formula before they are run through this formula.  
 
WhileReadingRecords;

NumberVar Days := {@Business Days Formula};  // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00");      // The start your work day
TimeVar SetEnd   := TimeValue("17:00");      // The end your work day
TimeVar StartTime := TimeValue({Start.Time});// The data field that holds your Start Time
TimeVar EndTime   := TimeValue({End.Time});  // The data field that holds your End Time

//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM.  Of course, this won't work if your workday is over 12 hours.
If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
If EndTime   < (SetEnd - 43200) then EndTime   := EndTime   + 43200;

Days * ((SetEnd - SetStart) / 3600)
-  ((SetEnd     - EndTime)  / 3600)
-  ((StartTime - SetStart)  / 3600)