?
Solved

Calculating Working Time Difference

Posted on 2005-04-04
10
Medium Priority
?
1,029 Views
Last Modified: 2007-11-27
Ok guys/girls, I'm over my head.  I retrieved the following code from a link on one of my previous questions regarding this topic.  See Below.

//@BusinessHoursIncludingHolidays
//ENTER FIRST DATETIME
datetimevar Start:= {tblApplications.DateAppEntryStart};
//ENTER END DATE TIME
datetimevar End:= {tblTrAppDetProcStats.DateTimeStatus};
//ENTER BUSINESS START TIME
timevar defaultstarttime:= time(9,0,0);
//ENTER BUSINESS END TIME
timevar defaultendtime:= time(17,0,0);
//ENTER WORKING DAYS
stringvar workingdays:= "-23456-";
//---------------------------------------------------------
datevar startdate:= date(Start);
timevar starttime:= time(Start);
datevar enddate:= date(End);
timevar endtime:= time(End);
numbervar HrsInAWorkDay:= hour(defaultendtime) - hour(defaultstarttime);
//---------------------------------------------------------
//if startdate falls on a non working day, make startdate //the next working day.
While wdaysClass1wdIsWorkDay (startdate, workingdays) = FALSE do
    (startdate:= startdate +1; starttime:=defaultstarttime);
//---------------------------------------------------------
//if enddate falls on a non working day, make enddate the //next working day.


While wdaysClass1wdIsWorkDay (enddate, workingdays) = FALSE do                            
    (enddate:= enddate +1; endtime:=defaultstarttime);


//---------------------------------------------------------
//if endtime or starttime are not within business hours,
//assign it to the defaulttime
if endtime < defaultstarttime then
endtime:= defaultstarttime;
if endtime > defaultendtime then
endtime:= defaultendtime;
if starttime < defaultstarttime then
starttime:= defaultstarttime;
if starttime > defaultendtime then
starttime:= defaultendtime;
//---------------------------------------------------------
//calculate totalworkdays
numbervar totaldays:= wdaysclass1wdnumworkdays(startdate,enddate,workingdays);
//---------------------------------------------------------
//Calculate hours in full days (subtract 1st and last day * //hours in a workday)
numbervar hours_fulldays:= (totaldays - 2) * HrsInAWorkDay;
//---------------------------------------------------------
//Calculate hours in first day
numbervar hours_firstday:=
hour(defaultendtime) - ((hour(starttime)*60) +minute(starttime))/60;
//---------------------------------------------------------
//Calculate hours in last day
numbervar hours_lastday:=
(hour(endtime) + (minute(endtime) / 60)) - hour(defaultstarttime);
//---------------------------------------------------------
//Business hours = fulldays + firstday + lastday
hours_fulldays + hours_firstday + hours_lastday;

At the "While wdaysClass1wdIsWorkDay (enddate, workingdays) = FALSE do....actually right before "wdays..." I'm getting the message "number, currency amount, boolean, date, time or string is expected here" What is wrong...keep it SIMPLE please.  

My two fields {tblApplications.DateAppEntryStart} and {tblTrAppDetProcStats.DateTimeStatus} are the only thing I've changed in this sample formula.  They are both "number" fields.  Ultimately I want to change the returned value to a time format.

THNX
0
Comment
Question by:frankrentef
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 13701999
Is the UFL with the class in the appropriate directory for Crystal?  Is it registered?

mlmcc
0
 

Author Comment

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

THNX
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13704014
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:frankrentef
ID: 13707969
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
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13713673
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
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 200 total points
ID: 13915148
He has the answer but a modification to the UFL is required to allow for work being done outside the 0900-1700 window.

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 14001147
Glad i could help

mlmcc
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question