Adding Business Hours/Minutes to a Start DateTime

Saturday 12 July 2008 @ 2:34 pm

A customer recently asked me to adapt formula 17 on my site so that they could add business hours or minutes instead of just business days. In their example they wanted to add 90 minutes to a DateTime value when a case was opened. If the DateTime was within 90 minutes of the end of the workday, they wanted to skip time when the shop was closed and pick up the 90 minutes the next business morning. So a case the opened at 4pm on Friday would be due 90 “work minutes” later, at 9:30 am on Monday. They had a 9-5 workday. They also wanted to skip holidays.

So below is my adaptation of formula 17 to allow you to define your workday start and end and then set the number of minutes to add. The customer is still running tests to confirm that it works in all situations but so far it looks like it works.

// User Inputs
DateTimeVar DateIn := {@Input Date Time}; //Your Date Field
Local NumberVar MinToAdd := 90; //minutes to add
Local TimeVar StartTime := Time (8,0,0); //Start of your Business Day
Local TimeVar EndTime := Time (17,0,0);//End of your Business Day

DateVar Array Holidays;
DateVar target := Date (DateIn);
Local NumberVar MinBefore := (StartTime - Time (0,0,0)) / 60;
Local NumberVar NetDay := (EndTime - StartTime) / 60;
Local NumberVar MinStart:= ((DateIn - target) * 1440) - MinBefore;

Local NumberVar Add := Truncate((MinToAdd+MinStart)/NetDay,0);
Local NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);

NumberVar MinRemain := MinToAdd + MinStart - (Add * NetDay) ;
DateAdd ('n' , (MinRemain + MinBefore ) , DateTime (target))

Leave a Reply

Recrystallize Pro

Crystal Reports Server