Date Ranges, in Hours, Exclude Wkends and Holidays

[DReese]

I need a formula that will calculate our open and close times in hours not days. This is the formula I am using:
DateTimeVar Start := {Help_Desk.Open Date & Time}; //Replace this field with your Starting Date field
DatetimeVar End := {Help_Desk.Close Date & Time}; //Replace this field with your Ending Date field
NumberVar Hours;
NumberVar Weeks;
NumberVar Days;
NumberVar Hol:= 0;


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) //adjust for starting on a Sunday:
+ (if DayOfWeek(End) = 7 then -1 else 0); //adjust for ending on a Saturday:


//Adjust for Holidays in the period between the start and end dates:
if Date(2001,11,22) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2001,11,23) in start to end then Hol:= Hol+1 else Hol:= Hol;

Weeks + Days - Hol
It is accurate in days, but I need actual hours.

 

[rosemaryl]

If you are using Crystal 8.0 this formula should work for you. The formula will display the age of the call in Days Hours Minutes and Seconds. I am assuming you are using MagicTSD and I have added a check for the state of the call (Open or Closed) which will allow you to show the age for open and closed calls.

Make sure that your report options have the time field set to 24 Hour time so that numbers display correctly!

Hope this helps you, if not contact me at rosemary@microflo.com and I will be happy to help you with a formula.



//Formula starts here
Local DateTimeVar d1 := {Help_Desk.Open Date & Time};
Local DateTimeVar d2;
Local Numbervar Hol := 0;
if{Help_Desk.State:}="C" then d2:={Help_Desk.Close Date & Time} else d2 := CurrentDateTime;

//Adjust for Holidays in the period between the start and end dates


if Date(2001,11,22) in d1 to d2 then Hol:= Hol+1 ;

if Date(2001,11,23) in d1 to d2 then Hol:= Hol+1 ;


CSTR( //CSTR is similar to ToText, and is used here to format the number of days returned without decimal places
DateDiff("d", d1, d2) - Hol-
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday)
,0) //the part of the formula that strips the decimals
& ":" &
Time(0,0,0) + datediff("s",d1,d2)

Rosemary Lieberman
rosemary@microflo.com

http://www.microflo.com

 

[chelseatech]

Also check out the BusinessDays UFL avaialble from Crystal Decisions - it supports a list of public holidays and you can ask if a date is a public holiday or how many businessdays between two dates. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kenhamady]

The formula you have referenced is mine, and I have also written instructions for converting to hours:

http://www.kenhamady.com/form13.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com