Need help with calculating business hours

[maddyyikes]

Hi,
I obtained the following code snippet from this site used for calculating business hours between the opening and closing of a ticket. The code seems to accurately count business hours for all tickets opened during the business hours. However, the code gives me incorrect values for any ticket opened before 8 AM or after 5 PM (business hours). For instance, if the open time is 7:00:00 PM (after business hours) and the close time is 3:00 PM next day then it should reflect 7 business hours as the total duration for the handling of the ticket (next day 8 AM to 3 PM = 7 business hours). However it gives me unusual values such as 1 day 13 hours which is not correct. Please let me know as to how I should modify the code given below to incorporate business hour calculation for tickets opened after business hours. This is extremely urgent and any help in this regard will be greatly appreciated. Thanks in advance. The code snippet is given below as follows:

local numbervar numDayStartHour:=8; //The hour that work starts in 24 hour time.
local numbervar numDayStartMinute:=0; //The minutes value of the time when work starts
local numbervar numDayEndHour:=17; //The hour that work ends in 24 hour time
local numbervar numDayEndMinute:=0; //The minutes value of the time when work ends

local numbervar numWholeDaysBetween; //The whole days between two datetimes
local timevar tmDayStart:=time(numDayStartHour,numDayStartMinute,0); //Convert the start hour and minutes to a time
local timevar tmDayEnd:=time(numDayEndHour,numDayEndMinute,0); //Convert the end hour and minutes to a time
local numbervar numSecondsInWholeDay; //Minutes in a whole day based on tmDayStart and tmDayEnd
local numbervar numSecondsInStartDay;
local numbervar numSecondsInEndDay;
local numbervar numTotalSeconds;
local numbervar numDivider:=60; //This is here in case we want to switch our formula over to seconds, later.


//whole days between, excluding weekends
// this is a pretty standard method
numWholeDaysBetween:=datediff("d",{probsummarym1.open.time},{probsummarym1.close.time})
-
(DateDiff ("ww", {probsummarym1.open.time}, {probsummarym1.close.time}, crSunday) +
DateDiff ("ww", {probsummarym1.open.time}, {probsummarym1.close.time}, crSaturday));



//Calculate seconds in a whole day
numSecondsInWholeDay:=(tmDayEnd-tmDayStart);

//Calculate seconds in start day
// beginning time to tmDayEnd
numSecondsInStartDay:=
(
(tmDayEnd-(maximum((tmDayStart) to (time({probsummarym1.open.time}))))) //The maximum is used to determine if we're inside the workday
);

//Calculate seconds in end day
// tmDayStart to End Time
numSecondsInEndDay:=
(
((minimum((time({probsummarym1.close.time})) to tmDayEnd))-tmDayStart) //The minimum is used to determine if we're inside the workday
);

//Calculate all seconds:
numTotalSeconds:=
numSecondsInEndDay+numSecondsInStartDay //First and Last day minutes
+
(((numWholeDaysBetween)-1)*numSecondsInWholeDay); //Whole days between minutes. The -1 is there because of the first and last day being included above.


numbervar tsecs1 := numTotalSeconds;
numbervar ndays1 := truncate(tsecs1/86400);
tsecs1 := remainder(tsecs1,86400);
numbervar nhours1 := truncate(tsecs1/3600);
tsecs1 := remainder(tsecs1,3600);
numbervar nmin1 := truncate(tsecs1/60);
tsecs1 := remainder(tsecs1,60);

totext(ndays1,0)+" days "+totext(nhours1,0)+" hours " + totext(nmin1,0) + "minutes"

 

[Turkbear]

Hi,
Although I cannot actually write the code needed ( not enough time to really understand the existing code), it would involve testing the Start time and , if after hours, compute the duration using the next day's date and 8 am as the 'actual' start date and time..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[maddyyikes]

Could anyone please elaborate on the modifications that may be needed to be done so as to make this code incorporate business hour durations for tickets opened after hours as I am still not able to make any progress regarding the same.

 

[123FakeSt]

I realize this is a Crystal forum but I have done this in Excel using the following equation.

=(((INT((INT(EndDate)-INT(StartDate))/7) + IF(WEEKDAY(StartDate)<=WEEKDAY(EndDate),0,1))*5)+(WEEKDAY(EndDate)-WEEKDAY(StartDate)))*780+((MAX(MIN(EndDate-INT(EndDate),18/24),5/24)-5/24)-(MAX(MIN(StartDate-INT(StartDate),18/24),5/24)-(5/24)))*1440

Of course EndDate refers to the cell with the end date and StartDate refers to the cell with the start date, and it is set to pick up from between 5am and 6pm, exclude weekends, but not holidays. It is also calculating minutes ....

Perhaps someone here can help rewrite it using Crystal syntax as I am a beginner. I was just hoping the logic would help. Maybe i'll have time to try to modify it myself tommorow.

The early bird gets the worm, but the second mouse gets the cheese.

 

[123FakeSt]

I'm getting close .. just don't know how to extract the time from a datetime in Crystal yet... Maybe I can figure it out tommorow.

//Define the start and end hours here ex. is 5am and 6pm
NumberVar startHour := 5;
NumberVar endHour := 18;

//Find complete weeks
((Int(DateDiff ('d',{ARDoc.DocDate},{ARDoc.DueDate})/7)
//Add on another week for the remainder
+ (iif(WEEKDAY({ARDoc.DocDate})<=WEEKDAY({ARDoc.DueDate}),0,1))) * 5
//Add on (or subtract if a remainder) the days different to get days apart
+ (WEEKDAY({ARDoc.DueDate}) - WEEKDAY({ARDoc.DocDate})))
//Multiply by the minutes for a business day
* (endHour - startHour) * 60 

//Find the min
+ ((maximum(minimum({ARDoc.DueDate}-INT({ARDoc.DueDate}),endHour/24),startHour/24) - startHour/24)
-(maximum(minimum({ARDoc.DocDate}-INT({ARDoc.DocDate}),endHour/24),startHour/24)-(startHour/24)))
*1440

The early bird gets the worm, but the second mouse gets the cheese.