Problem with working days calculation

[neilmcdonald]

Hi,

I'm using the following formula to calculate the number of working hours between two dates, however I've noticed a problem with the weekend part of the calculation. The formula rounds the number of weekend days up and is therefore not accurate. The formula was poached from the web somewhere, but I can't remember where! Has anybody else come across this problem?

//CALCULATE THE NUMBER OF BUSINESS HOURS
//BETWEEN FirstDateTime AND LastDateTime
//EXCLUDING HOLIDAYS IN THE TOTAL

//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED

//Replace datetime(2001,12,23,11,50,0) with your starting //date
DatetimeVar FirstDateTime:= {SCCall.Call_InDate};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= Minimum ({SCFSR.FSR_Start_Date}, {SCCall.Call_Num});
//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);

//Holidays need to be assigned to all the holidays that //need to be removed from the total

DateVar PublicHol1Yr1:=Date(2003,01,01);
DateVar PublicHol2Yr1:=Date(2003,01,02);
DateVar PublicHol3Yr1:=Date(2003,04,18);
DateVar PublicHol4Yr1:=Date(2003,04,21);
DateVar PublicHol5Yr1:=Date(2003,05,05);
DateVar PublicHol6Yr1:=Date(2003,05,26);
DateVar PublicHol7Yr1:=Date(2003,07,21);
DateVar PublicHol8Yr1:=Date(2003,09,29);
DateVar PublicHol9Yr1:=Date(2003,12,25);
DateVar PublicHol10Yr1:=Date(2003,12,26);

DateVar PublicHol1Yr2:=Date(2004,01,01);
DateVar PublicHol2Yr2:=Date(2004,01,02);
DateVar PublicHol3Yr2:=Date(2004,04,18);
DateVar PublicHol4Yr2:=Date(2004,04,21);
DateVar PublicHol5Yr2:=Date(2004,05,05);
DateVar PublicHol6Yr2:=Date(2004,05,26);
DateVar PublicHol7Yr2:=Date(2004,07,21);
DateVar PublicHol8Yr2:=Date(2004,09,29);
DateVar PublicHol9Yr2:=Date(2004,12,25);
DateVar PublicHol10Yr2:=Date(2004,12,26);

//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//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;
NumberVar Holidays:=0;
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 := (truncate((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else WeekEnds := 0;

//CALCULATE HOLIDAYS
//*********************************************************
//---- PUBLIC HOLIDAY 1 ----

//Yr1
If dayofweek(PublicHol1Yr1) <> 7 and dayofweek(PublicHol1Yr1) <> 1 then
(
If PublicHol1Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol1Yr2) <> 7 and dayofweek(PublicHol1Yr2) <> 1 then
(
If PublicHol1Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);


//---- PUBLIC HOLIDAY 2 ----

//Yr1
If dayofweek(PublicHol2Yr1) <> 7 and dayofweek(PublicHol2Yr1) <> 1 then
(
If PublicHol2Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol2Yr2) <> 7 and dayofweek(PublicHol2Yr2) <> 1 then
(
If PublicHol2Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);


//---- PUBLIC HOLIDAY 3 ----

//Yr1
If dayofweek(PublicHol3Yr1) <> 7 and dayofweek(PublicHol3Yr1) <> 1 then
(
If PublicHol3Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol3Yr2) <> 7 and dayofweek(PublicHol3Yr2) <> 1 then
(
If PublicHol3Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PUBLIC HOLIDAY 4 ----

//Yr1
If dayofweek(PublicHol4Yr1) <> 7 and dayofweek(PublicHol4Yr1) <> 1 then
(
If PublicHol4Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol4Yr2) <> 7 and dayofweek(PublicHol4Yr2) <> 1 then
(
If PublicHol4Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PUBLIC HOLIDAY 5 ----

//Yr1
If dayofweek(PublicHol5Yr1) <> 7 and dayofweek(PublicHol5Yr1) <> 1 then
(
If PublicHol5Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol5Yr2) <> 7 and dayofweek(PublicHol5Yr2) <> 1 then
(
If PublicHol5Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PUBLIC HOLIDAY 6 ----

//Yr1
If dayofweek(PublicHol6Yr1) <> 7 and dayofweek(PublicHol6Yr1) <> 1 then
(
If PublicHol6Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol6Yr2) <> 7 and dayofweek(PublicHol6Yr2) <> 1 then
(
If PublicHol6Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PUBLIC HOLIDAY 7 ----

//Yr1
If dayofweek(PublicHol7Yr1) <> 7 and dayofweek(PublicHol7Yr1) <> 1 then
(
If PublicHol7Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol7Yr2) <> 7 and dayofweek(PublicHol7Yr2) <> 1 then
(
If PublicHol7Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PUBLIC HOLIDAY 8 ----

//Yr1
If dayofweek(PublicHol8Yr1) <> 7 and dayofweek(PublicHol8Yr1) <> 1 then
(
If PublicHol8Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol8Yr2) <> 7 and dayofweek(PublicHol8Yr2) <> 1 then
(
If PublicHol8Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PUBLIC HOLIDAY 9 ----

//Yr1
If dayofweek(PublicHol9Yr1) <> 7 and dayofweek(PublicHol9Yr1) <> 1 then
(
If PublicHol9Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol9Yr2) <> 7 and dayofweek(PublicHol9Yr2) <> 1 then
(
If PublicHol9Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PUBLIC HOLIDAY 10 ----

//Yr1
If dayofweek(PublicHol10Yr1) <> 7 and dayofweek(PublicHol10Yr1) <> 1 then
(
If PublicHol10Yr1 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//Yr2
If dayofweek(PublicHol10Yr2) <> 7 and dayofweek(PublicHol10Yr2) <> 1 then
(
If PublicHol10Yr2 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//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 Holidays and Weekends
FinalDays:= Days - Holidays - 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;

 

[FVTrainer]

You might try this thread:

thread767-581641