Business Hours Problem

[brek2]

I am using Crystal 8.5 and need to calculate business hours, no weekends, including holidays. I found the sample report called BusinessDays_Hours (How To Calculate Business Hours or Business Days) in the zip file cr_business_dh_samples in the Business Objects knowledge base. The code for the field @MANUAL_BusinessHoursIncludeHolidays looks like it does exactly what I need. But when I use the code in my report, it doesn’t work as I expect.

My start date is a SQL date time field for an application and includes Month, Day, Year and time.
My end date is also a SQL date time field for a contact and only includes Month, Day, Year—no time is captured. The end date for a contact can be before the start date. In that case, I just want to display 0 hours because the purpose of the report is to calculate how long it takes for a contact after an application is made.

When Crystal displays the end date, it adds 12:00:00 AM for the time for every record.

Here is some sample data I am getting.

App Date Contact Date Hours
1. Tues 3/8/2005 10:43:56 AM Tues 3/8/2005 12:00:00 AM 9.0

2. Wed 2/23/2005 2:25:49 PM Mon 1/17/2005 12:00:00 AM 0.0

3. Wed 4/27/2005 9:47:08 PM Tues 4/26/2005 12:00:00 AM 9.0

4. Tues 1/18/2005 11:42:27 AM Wed 1/19/2005 12:00:00 AM 5.3


I would like 0 hours for number 1 and number 3 above. Number 2 and number 4 are right.

Ken Hammady’s formula did not work as he noted that his formula assumes that “the start and end times are within your working day.” In my case, applications are made 24/7 on line (start date) and Crystal adds the 12:00:00 AM to the end date.

I hope someone can help.

Thanks.

 

[BobSuruncle]

I don't have Ken's formula in front of me,
but couldn't you add some logic to the beginning, like this:

If {Table.EndDateTime} < {Table.StartDateTime} then 0 
Else

Insert Ken's logic here

Bob Suruncle

 

[brek2]

Yes, thank you, BobSuruncle. I placed this in the Business Objects formula like this:

IF FirstDateTime <=Date(0,0,0) or LastDateTime <=Date(0,0,0) or LastDateTime < FirstDateTime then hours:= 0

It works for contact dates and times that are before the app dates and times but I’m still getting data like this.

App Date Contact Date Hours

Mon 1/10/2005 8:14:44 PM Tues 1/11/2005 12:00:00 AM 9


This should be 0.

There is a note in Ken Hammady’s formula 13 that says “records that start and end ‘after hours’ …..need to be adjusted in a separate formula before they are run through” his formula. Where is this separate formula? I can try Ken’s formula if I have that.

Thanks.

 

[BobSuruncle]

If it's true that ALL instances where {Table.ContactDate} has a time portion of midnight can be set to 0, then add that condition to your If Then Else .


Bob Suruncle

 

[brek2]

Can you be more specific? Would I use something like

If Time(LastDateTime) = ????? then ????

 

[BobSuruncle]

Try this:

If 
(
Time(LastDateTime) = Time(00,00,00) 
or FirstDateTime <=Date(0,0,0) 
or LastDateTime <=Date(0,0,0) 
or LastDateTime < FirstDateTime 
)
then hours:= 0

Bob Suruncle

 

[brek2]

Here is the entire formula from Business Objects. When I added your code (to the section below titled "Finish formula if FirstDateTime or LastDateTime is null"), I get 0 hours for everything!!

//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:= {@START DATE};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= {@End Date};
//Replace Time(09,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(08,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
(
Time(LastDateTime) = Time(00,00,00)
or FirstDateTime <=Date(0,0,0)
or LastDateTime <=Date(0,0,0)
or LastDateTime < FirstDateTime
)
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;

 

[BobSuruncle]

Sorry, I forgot what your original post said.
All of your End Dates have a time portion of 00.

Remove the check for Time(LastDateTime) = Time(00,00,00)

This will always be true and this is why you're getting 0.
(I must be getting old)

In your first response to my response (hmmm) you give an example where the time portion of the App Date is after hours. Is this why it should be 0? (sorry to be so thick)
Perhaps you could run a check to see if the Time portion of App Date is after 17:00

something like this

If 
(
Time(FirstDateTime) > BusinessEndTime 
or FirstDateTime <=Date(0,0,0) 
or LastDateTime <=Date(0,0,0) 
or LastDateTime < FirstDateTime 
)
then hours:= 0

Bob Suruncle

 

[brek2]

It needs to be 0 hours when the Contact Date (End Date) is before the App Date (Start Date). Your suggestion (thank you) worked for that.

It’s the following that’s puzzling:


App Date Contact Date Hours

1. Mon 1/10/2005 8:14:44 PM Tue 1/11/2005 12:00:00 AM 9.0

From Mon night at 8 PM to midnight should be 0 but its counting it as an entire day plus adding an hour.



Here is some puzzling data from the Business Objects sample report (from their knowledge base) where I found the code:

Start Date End Date MANUAL_BusinessHoursIncludeHolidays

Tue, Dec 31, 1996 3:07:12 AM Tue, Dec 31, 1996 6:14:24 AM 8.00

Tue, Dec 31, 1996 4:48:00 AM Wed, Jan 1, 1997 9:36:00 AM 8.60

The first is obviously wrong. From 3 to 6 AM on the same day should be 0 hours. The second is right. It counts Tue as an entire 8 hour day plus .6 for Wed (starting at 9 am). The problem seems to come when the date is the same and the time is before or after hours, but I haven’t pin pointed where in the code this is happening.

 

[brek2]

I FOUND THE ANSWER!!!!

It's from lbass in thread767-891598 titled "Crystal official formula bug? Or just me?"

lbass recommended a couple of changes to the Business Objects formula and along with Bobsuruncle's first suggestion, I have the report I need.

THANK YOU!