Business Hours

[craigK]

i want modify the standard cyrstal business hours formula take into account weekends. the standard formula assumes you are closed on Sat and Sun. Our business is open on Sat and Sun and has different hours on both days that are also different from the weekday hours. has anyone already done something like this?

//CALCULATE THE NUMBER OF BUSINESS HOURS
//BETWEEN FirstDateTime AND LastDateTime
//INCLUDING HOLIDAYS IN THE TOTAL

//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED

//Replace datetime(2001,12,23,11,50,0) with your starting //date
DatetimeVar FirstDateTime:= {test.TimeReceived};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= {test.TimeAnswered};
//Replace Time(07,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(08,00,00);
//Replace Time(15,00,00) with your business ending time
TimeVar BusinessEndTime:= Time(17,00,00);

//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//END INPUT REQUIRED _ END INPUT REQUIRED _ END INPUT

//Other variables used in the formula
Numbervar HoursInADay:= (BusinessEndTime - BusinessStartTime)/3600;
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar halfdays;
NumberVar fulldays;
NumberVar hours;
timevar TrueStartTime;
timevar TrueEndTime;



//BEGIN FORMULA:
//*********************************************************

//FINISH FORMULA IF FirstDateTime OR LastDateTime IS NULL
//*********************************************************
IF FirstDateTime <=Date(0,0,0) or LastDateTime <=Date(0,0,0) then hours:= 0

//ELSE ASSIGN HOURS
//*********************************************************
ELSE
(
//ASSIGN FirstDateTime and LastDateTime
//*********************************************************
//Determine whether FirstDateTime falls within
//Start Time to End Time
if time(FirstDateTime) in BusinessStartTime to BusinessEndTime then
FirstDateTime:= FirstDateTime
else if time(FirstDateTime) > BusinessEndTime then
FirstDateTime:= datetime(date(FirstDateTime)+1, BusinessStartTime)
else if time(FirstDateTime) < BusinessStartTime then
FirstDateTime:= datetime(date(FirstDateTime), BusinessStartTime);

//Determine whether LastDateTime falls within Start Time to End Time
if time(LastDateTime) in BusinessStartTime to BusinessEndTime then
LastDateTime:= LastDateTime
else if time(LastDateTime) > BusinessEndTime then
//changed the following from: LastDateTime:=
//datetime(date(LastDateTime), BusinessEndTime)
LastDateTime:= datetime(date(LastDateTime)+1, BusinessStartTime)
else if time(LastDateTime) < BusinessStartTime then
//changed the following from: LastDateTime:=
//datetime(date(LastDateTime)-1, BusinessEndTime);
LastDateTime:= datetime(date(LastDateTime), BusinessStartTime);


//ASSIGN STARTDATE and ENDDATE
//*********************************************************
//if the first day falls on a weekend, StartDate is equal //to the following Monday for calculation reasons
If DayOfWeek(FirstDateTime) = 7 Then
StartDate := date(FirstDateTime) + 2
Else If DayOfWeek(FirstDateTime) = 1 Then
StartDate := date(FirstDateTime) + 1
Else StartDate:=date(FirstDateTime);

//if the last day falls on a weekend, EndDate is equal to //the following Monday for calculation reasons
If DayOfWeek(LastDateTime) = 7 Then
EndDate := date(LastDateTime) + 2
Else If DayOfWeek(LastDateTime) = 1 Then
EndDate := date(LastDateTime) + 1
Else EndDate := date(LastDateTime);

//CALCULATE DAYS AND WEEKENDS
//*********************************************************
//Calculate Days (including First day and Last day)
Days:= (EndDate - StartDate)+1;

//Calculate weekends
if Days >= 7 then
WeekEnds := (Round((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else WeekEnds := 0;

//CALCULATE FINALDAYS
//*********************************************************
//If the Last Day is on a weekend then FinalDays subtract the weekend days
If DayOfWeek(LastDateTime) = 7 then FinalDays:= FinalDays - 1;
If DayOfWeek(LastDateTime) = 1 then FinalDays:= FinalDays - 2;

//Assign FinalDays to Days minus Weekends
FinalDays:= Days - WeekEnds;

//CALCULATE HOURS
//*********************************************************
//Calculate FirstDateTime and LastDateTime if falling on a weekend
//if the first day falls on a weekend, StartDate is equal //to the following Monday for calculation reasons
If DayOfWeek(FirstDateTime) = 7 Then
FirstDateTime := datetime(date(FirstDateTime) + 2, BusinessStartTime)
Else If DayOfWeek(FirstDateTime) = 1 Then
FirstDateTime := datetime(date(FirstDateTime) + 1, BusinessStartTime);

//if the last day falls on a weekend, EndDate is equal to //the following Monday for calculation reasons
If DayOfWeek(LastDateTime) = 7 Then
LastDateTime := datetime(date(LastDateTime) + 2,BusinessStartTime)
Else If DayOfWeek(LastDateTime) = 1 Then
LastDateTime := datetime(date(LastDateTime) + 1, BusinessStartTime);

//If less than 24 hours involved
If FinalDays <= 1 then
(
//If first day is the same day as last day
if date(FirstDateTime) = date(LastDateTime) then
(
//If First Day starts before business start time, //assign TrueStartTime to business starttime
if time(FirstDateTime) >= BusinessStartTime then
TrueStartTime:= time(FirstDateTime)
else TrueStartTime:= BusinessStartTime;

//If Last Day ends after business end time, assign //TrueEndTime to business endtime
if time(LastDateTime) <= BusinessEndTime then
TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime
)
//If first day is not the same day as last day
else
TrueStarttime:= BusinessStartTime;

if time(LastDateTime) <= BusinessEndTime then
TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime;

//Assign hours to the endtime - starttime
//divided by 3600 (seconds in an hour)
hours:= (TrueEndTime-TrueStartTime)/3600;
)

//Else hours = how many hours on the two half days + how //many hours for the full days
Else
(
halfdays:= ((BusinessEndTime - time(FirstDateTime)) /3600 + (time(LastDateTime) - BusinessStartTime)
/3600);
fulldays:= (FinalDays-2) * HoursInADay;
hours:= halfdays + fulldays;
);
);

//DISPLAY NUMBER OF BUSINESS HOURS IN THE RANGE
//*********************************************************hours;