[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4356
  • Last Modified:

Business Hours calculation Excluding Weekends in Crystal XI

Greetings Everyone,

I have some problem with calculating Business hours in crystal XI. Following is a sample out of data. our business time is from 07:00 hrs to 16:00 hrs and business days are from Sat-Wed, weekends are Thursday and Friday.

so the calculation of hours should exclude Thursday and Friday and any hours before 7:00 AM and after 04:00 PM.

following is the sample output of calculation @Business.Hours.

Date1: Start Date
Date2: End Date

also note that the entries in the Date2 field were intentionally added that way to explain the problem. this problem seems to be when the Date1t and Date2 are on Thursdays and is during non business hours. this gives the wrong result. instead of giving 0 hours it is showing me 9 hours.

ID              Date1                             Date2               @Business.Hours
1        1/3/08  3:00 AM         1/3/08  3:00 AM        9.00
2        1/3/08  3:00 AM         1/3/08  2:59 AM        9.00
3        1/3/08  3:00 PM          1/3/08  3:00 PM         0.00
4        1/3/08  3:00 PM          1/3/08  2:59 PM         0.00
5        1/4/08  3:00 AM         1/4/08  3:00 AM        0.00
6        1/4/08  3:00 AM         1/4/08  2:59 AM        0.00
7        1/4/08  3:00 PM          1/4/08  3:00 PM         0.00
8        1/4/08  3:00 PM          1/4/08  2:59 PM         0.00
9        1/5/08  7:00 AM         1/5/08  2:00 PM         7.00
10       1/5/08  7:00 AM        1/5/08  6:59 AM        0.00
11       1/5/08  7:00 AM        1/10/08  7:00 AM      45.00
12       1/5/08  7:00 AM        1/11/08  11:00 PM      45.00
13       1/10/08  2:00 AM       1/12/08  2:00 AM      0.00
14       1/10/08  2:00 AM       1/12/08  8:00 AM      1.00

Original formula is downloaded from http://support.businessobjects.com/ and modified to suit weekend and business hours.

the modified formula looks like below.

//@Business.Hours.
//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:= {Orders.Date1};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= {Orders.Date2};
//Replace Time(09,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(07,00,00);
//Replace Time(17,00,00) with your business ending time
TimeVar BusinessEndTime:= Time(16,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
    LastDateTime:= datetime(date(LastDateTime), BusinessEndTime)
else if time(LastDateTime) < BusinessStartTime then
    LastDateTime:= datetime(date(LastDateTime)-1, BusinessEndTime);

//ASSIGN STARTDATE and ENDDATE
//*********************************************************
//if the first day falls on a weekend,

If DayOfWeek(FirstDateTime) = 5 Then
    StartDate := date(FirstDateTime) + 2
Else If DayOfWeek(FirstDateTime) = 6 Then
    StartDate := date(FirstDateTime) + 1
Else StartDate:=date(FirstDateTime);

//if the last day falls on a weekend,

If DayOfWeek(LastDateTime) = 5 Then
    EndDate := date(LastDateTime) + 2
Else If DayOfWeek(LastDateTime) = 6 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) = 5 then FinalDays:= FinalDays - 1;
If DayOfWeek(LastDateTime) = 6 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) = 5 Then
    FirstDateTime := datetime(date(FirstDateTime) + 2, BusinessStartTime)
Else If DayOfWeek(FirstDateTime) = 6 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) = 5 Then
    LastDateTime := datetime(date(LastDateTime) + 2,BusinessStartTime)
Else If DayOfWeek(LastDateTime) = 6 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;

any help to fix this would be appreciated.






//@Business.Hours.
//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:= {Orders.Date1}; 
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= {Orders.Date2}; 
//Replace Time(09,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(07,00,00); 
//Replace Time(17,00,00) with your business ending time
TimeVar BusinessEndTime:= Time(16,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 
    LastDateTime:= datetime(date(LastDateTime), BusinessEndTime)
else if time(LastDateTime) < BusinessStartTime then 
    LastDateTime:= datetime(date(LastDateTime)-1, BusinessEndTime);
 
//ASSIGN STARTDATE and ENDDATE
//*********************************************************
//if the first day falls on a weekend, 
 
If DayOfWeek(FirstDateTime) = 5 Then 
    StartDate := date(FirstDateTime) + 2
Else If DayOfWeek(FirstDateTime) = 6 Then 
    StartDate := date(FirstDateTime) + 1
Else StartDate:=date(FirstDateTime);
 
//if the last day falls on a weekend, 
 
If DayOfWeek(LastDateTime) = 5 Then 
    EndDate := date(LastDateTime) + 2
Else If DayOfWeek(LastDateTime) = 6 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) = 5 then FinalDays:= FinalDays - 1;
If DayOfWeek(LastDateTime) = 6 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) = 5 Then 
    FirstDateTime := datetime(date(FirstDateTime) + 2, BusinessStartTime)
Else If DayOfWeek(FirstDateTime) = 6 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) = 5 Then 
    LastDateTime := datetime(date(LastDateTime) + 2,BusinessStartTime)
Else If DayOfWeek(LastDateTime) = 6 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;

Open in new window

0
md5auth
Asked:
md5auth
  • 4
  • 3
1 Solution
 
mlmccCommented:
I took a quick lok at it and I don't see the error.

Here is a link to an easier formula
http://www.kenhamady.com/form01.shtml

mlmcc
0
 
md5authAuthor Commented:
HI mlmcc:

I tried the code at http://www.kenhamady.com/form01.shtml.

but its giving me strange results. may be i did not modify it as needed.

is it possible for you to modify the code for weekends (Thursday and Friday) and working hours (7:00 am to 4:00 pm)

thanks.
 
 
0
 
mlmccCommented:
I'll try

mlmcc
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
md5authAuthor Commented:
mlmcc;

Did you get any chance to work on this. i hope to get some results from you.
0
 
mlmccCommented:
I have worked on it with little success.  I'll try further tonight.

mlmcc
0
 
md5authAuthor Commented:
hi mlmcc;

any update on the subject matter. i wonder what on earth is wrong with the code. loosing hope :)
0
 
mlmccCommented:
Try the formulas in this report

Save the report and change the extension to rpt.  They seem to work for all instances you provide except the one where the entire period is on thursday and friday.
13       1/10/08  2:00 AM       1/12/08  2:00 AM      0.00

mlmcc
BusinessHours.txt
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now