Calculating Working Time Difference

Is the UFL with the class in the appropriate directory for Crystal?  Is it registered?

mlmcc

UFL?  It is a registered copy of Crystal but I'm not sure what you mean by UFL?

THNX

UFL - User Function Library.  as I recall the functions you are using are in one and not a part of the Crystal install.  I might be wrong on that.

mlmcc

Here is a link

http://support.businessobjects.com/library/kbase/articles/c2005297.asp

businessdays.zip
http://support.businessobjects.com/communityCS/FilesAndUpdates/businessdays.zip.asp

mlmcc

When I use the following (from one of the links above)


-------------------
//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:= {tblApplications.DateAppEntryStart};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= {tblTrAppDetProcStats.DateTimeStatus};
//Replace Time(09,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(09,00,00);
//Replace Time(17,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
    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;

---------------------------


I get the value (Number) 1.01 from the values 3/1/2005 6:55:23 a.m. and 3/1/2005 10:00:19 a.m.  I know the "number" value needs to be converted to a time value, but when I do this using the "CTime" function or other conversion types I still do not get the correct time difference (allowing for work times of 9:00 - 5:00 / Monday thru Friday)  

Any ideas on what I'm doing incorrectly?

THNX

That is the number of hours.  Unfortunately the code is using the 9-5 hours and there is only from 09:00:00 - 10:00:19 or about 1.01 hours.

mlmcc

Glad i could help

mlmcc