Datediff (business hours only) year issue -- what am I missing?!

[jdemmi]

I am using the formula below to determine the amount of time (business hours only) between 2 date fields. It works fine except when the start date is in a different year then the end date. Like start date = 10/1/2004 and end date = 1/1/2005. In these cases the value returned is far too low. It seems like the formula is not considering the year at all. What am I missing?

PS -- this forumla was hijacked from a website (can't remember which)

Here is the ENTIRE 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:= {REVHDP_CALLS_FULL_TEXT_2.Call_DateEntered};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= {RessourceLog.SysLog_Date};
//Replace Time(07,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(08,00,00);
//Replace Time(15,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;

If {REVHDP_CALLS_FULL_TEXT_2.LST_CALLPRIO} in ["Level 1", "Level 2"] then
datediff("n",{REVHDP_CALLS_FULL_TEXT_2.Call_DateEntered},{RessourceLog.SysLog_Date})
else

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


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

-- Jason
"It's Just Ones and Zeros

 

[smcnulty2000]

Okay, that's just scary. Here's what I would use:



//
local numbervar numDayStartHour:=8; //The hour that work starts in 24 hour time.
local numbervar numDayStartMinute:=0; //The minutes value of the time when work starts
local numbervar numDayEndHour:=17; //The hour that work ends in 24 hour time
local numbervar numDayEndMinute:=0; //The minutes value of the time when work ends

local numbervar numWholeDaysBetween; //The whole days between two datetimes
local timevar tmDayStart:=time(numDayStartHour,numDayStartMinute,0); //Convert the start hour and minutes to a time
local timevar tmDayEnd:=time(numDayEndHour,numDayEndMinute,0); //Convert the end hour and minutes to a time
local numbervar numMinutesInWholeDay; //Minutes in a whole day based on tmDayStart and tmDayEnd

local numbervar numMinutesInStartDay;
local numbervar numMinutesInEndDay;
local numbervar numTotalMinutes;
local numbervar numDivider:=60; //This is here in case we want to switch our formula over to seconds, later.


//whole days between, excluding weekends
// this is a pretty standard method
numWholeDaysBetween:=datediff("d",{@DateStart},{@DateEnd})
-
(DateDiff ("ww", {@DateStart}, {@DateEnd}, crSunday) +
DateDiff ("ww", {@DateStart}, {@DateEnd}, crSaturday));



//Calculate minutes in a whole day
numMinutesInWholeDay:=(tmDayEnd-tmDayStart)/numDivider;

//Calculate minutes in start day
// beginning time to tmDayEnd
numMinutesInStartDay:=
(
(tmDayEnd-maximum(tmDayStart to timevalue({@DateStart})))/numDivider //The maximum is used to determine if we're inside the workday
);

//Calculate minutes in end day
// tmDayStart to End Time
numMinutesInEndDay:=
(
((minimum(timevalue({@DateEnd}) to tmDayEnd))-tmDayStart)/numDivider //The minimum is used to determine if we're inside the workday
);

//Calculate all minutes:
numTotalMinutes:=
numMinutesInEndDay+numMinutesInStartDay //First and Last day minutes
+
(((numWholeDaysBetween)-1)*numMinutesInWholeDay); //Whole days between minutes. The -1 is there because of the first and last day being included above.

//Get the hours from the minutes
numTotalMinutes/60;


The idea here is that there is a set amount of time in each day that is work time. We are going to get the time between two datestamps that are whole days, and count them up. We subtract out weekends using one of the standard methods.

Then we assume that both ends are potentially partial days, so we figure out each of them on their own.

With those days at each end, we have to figure out if the time is within the workday frame. If we find the maximum of two values, our start time and the work start time, it will retrieve whichever one of those is later. We can check the end time in exactly the same way using the minimum.

I used minutes because that's closer to what I'm used to, and it gives me more flexibility later if someone wants a different version.

Including holidays would require the code knowing what days were holidays, but that's not too hard either. You can hard code it using a line that simply subtracts a whole day if it happens to fall between.


//An example holiday, hard coded.
if #7/4/2005# in [{@DateStart} to {@DateEnd}]
then numTotalMinutes:=numTotalMinutes-numMinutesInWholeDay;


If you want to not hard code the holiday part because you've got an immense date range or you don't want to do the hard coding, there are formulas running around for figuring out each holiday that you might want. You can also plug them into a table on your server and retrieve them. Just make sure you don't put in a holiday that happens to be outside your work range or you'll subtract a day that you shouldn't.

This does work in version 10. I'm not sure which version you are using.

Obviously, I would put in something to check nulls. But I might just do that in another formula to keep this one as clear as possible.

scottm.

 

[jdemmi]

Thanks...but I actually had already fixed my problem. Here's how..

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_business_days_hours.zip.asp

Thanks again anyway!

-- Jason
"It's Just Ones and Zeros