Crystal official formula bug? Or just me? 1

[dscheste]

Hello, gurus

I haev been working with Crystal 9 lately because of its incredible job in time calculations and report capabilities.

Crystal released a whitepaper on the the formulas, which work with the businesshours.

Well, I have used this formula and then tried to check all the records.

I was using the manual formula from the whitepaper, which does not take into consideration holidays.

Here is the formula:

//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:= datetime(2004,7,7,6,37,31); 
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= datetime(2004,7,7,6,43,46); 
//Replace Time(07,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(07,00,00); 
//Replace Time(15,00,00) with your business ending time
TimeVar BusinessEndTime:= Time(15,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, 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;

As you might se, I have used 6,37,31 as start time and 6,43,46 as end time on the same business day 2004,7,7.

The result the formula returs is 8 hours.

The true result is supposed to be 0 as the start of business day has not yet occured.

TimeVar BusinessStartTime:= Time(07,00,00);
//Replace Time(15,00,00) with your business ending time
TimeVar BusinessEndTime:= Time(15,00,00);

Can anybody advise as how to fix the bug?


The same bug appears when the times are the same day afterhours, i.e. start time is 15,37,31 and end time is 15,43,46

 

[lbass]

I'm not sure why you would think this was a "bug" in CR, rather than a formula that doesn't work quite right for you.

The reason you are getting the 8-hour difference is because of the section where you are assigning FirstDateTime and LastDateTime. Note that the formula sets LastDateTime to the previous day when it is less than the start time, so that later in the formula, when it is looking only at time, it is picking up 3:00, not 7:00. Similarly, when LastDateTime is greater than 3:00, it is setting to 3:00 of the same day, instead of 7:00 of the next day. So you might try changing that section of the formula as follows:

//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);

-LB

 

[dscheste]

Thank you very much, I will try it.

I have already applied some changes to the logic in the formula, however, the formula is presented as a tool to calculate business hours between two time stamps.

It might be not a bug as the formula works, just the workflow is flawed.

Thank you again for the imput, I am still testing several approaches.

 

[lholodnak]

Can someone tell me how I take into consideration holidays into this formula. I got all of this to work and just need to remove holidays out of the mix. thankyou, Lisa

 

[IdoMillet]

See:

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

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com