Business Hours & half days??

[Monkeyboy126]

I need to calculate business days between two date fields. I operate an IT Helpdesk and our 'open' hours are 8.00 - 18.00 Mon to Fri and 8.00 - 13.00 Saturday.

I need to calculate business hours between two dates utilising only the hours above.

I have seen Ken's business hours formula, which works great for the Monday - Friday hours but how do I include the half day Saturday?

Any assistance would be greatly appreciated.

Thanks

Paul

 

[Trainingjason]

Copied from a sample file not mine

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



//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:= {@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(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 NewYearsDay1998:=Date(1998,01,01);
DateVar PresidentsDay1998:=Date(1998,02,15);
DateVar MemorialDay1998:=Date(1998,05,31);
DateVar IndependenceDay1998:=Date(1998,07,05);
DateVar LaborDay1998:=Date(1998,09,06);
DateVar ThanksgivingDay1998:=Date(1998,11,26);
DateVar DayAfterDay1998:=Date(1998,11,27);
DateVar ChristmasDay1998:=Date(1998,12,25);

DateVar NewYearsDay1999:=Date(1999,01,01);
DateVar PresidentsDay1999:=Date(1999,02,15);
DateVar MemorialDay1999:=Date(1999,05,31);
DateVar IndependenceDay1999:=Date(1999,07,05);
DateVar LaborDay1999:=Date(1999,09,06);
DateVar ThanksgivingDay1999:=Date(1999,11,25);
DateVar DayAfterDay1999:=Date(1999,11,26);
DateVar ChristmasDay1999:=Date(1999,12,25);
DateVar NewYearsDay2000:=Date(2000,01,01);
DateVar PresidentsDay2000:=Date(2000,02,15);
DateVar MemorialDay2000:=Date(2000,05,29);
DateVar IndependenceDay2000:=Date(2000,07,04);
DateVar LaborDay2000:=Date(2000,09,04);
DateVar ThanksgivingDay2000:=Date(2000,11,23);
DateVar DayAfterDay2000:=Date(2000,11,24);
DateVar ChristmasDay2000:=Date(2000,12,25);

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

//CALCULATE HOLIDAYS
//*********************************************************
//---- NEW YEARS DAY ----

//1998
If dayofweek(NewYearsDay1998) <> 7 and dayofweek(NewYearsDay1998) <> 1 then
(
If NewYearsDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(NewYearsDay1999) <> 7 and dayofweek(NewYearsDay1999) <> 1 then
(
If NewYearsDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(NewYearsDay2000) <> 7 and dayofweek(NewYearsDay2000) <> 1 then
(
If NewYearsDay2000 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- PRESIDENTS DAY ----

//1998
If dayofweek(PresidentsDay1998) <> 7 and dayofweek(PresidentsDay1998) <> 1 then
(
If PresidentsDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(PresidentsDay1999) <> 7 and dayofweek(PresidentsDay1999) <> 1 then
(
If PresidentsDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(PresidentsDay2000) <> 7 and dayofweek(PresidentsDay2000) <> 1 then
(
If PresidentsDay2000 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- MEMORIAL DAY ----

//1998
If dayofweek(MemorialDay1998) <> 7 and dayofweek(MemorialDay1998) <> 1 then
(
If MemorialDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(MemorialDay1999) <> 7 and dayofweek(MemorialDay1999) <> 1 then
(
If MemorialDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(MemorialDay2000) <> 7 and dayofweek(MemorialDay2000) <> 1 then
(
If MemorialDay2000 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- INDEPENDENCE DAY ----

//1998
If dayofweek(IndependenceDay1998) <> 7 and dayofweek(IndependenceDay1998) <> 1 then
(
If IndependenceDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(IndependenceDay1999) <> 7 and dayofweek(IndependenceDay1999) <> 1 then
(
If IndependenceDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(IndependenceDay2000) <> 7 and dayofweek(IndependenceDay2000) <> 1 then
(
If IndependenceDay2000 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- LABOR DAY ----

//1998
If dayofweek(LaborDay1998) <> 7 and dayofweek(LaborDay1998) <> 1 then
(
If LaborDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(LaborDay1999) <> 7 and dayofweek(LaborDay1999) <> 1 then
(
If LaborDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(LaborDay2000) <> 7 and dayofweek(LaborDay2000) <> 1 then
(
If LaborDay2000 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- THANKSGIVING ----

//1998
If dayofweek(ThanksgivingDay1998) <> 7 and dayofweek(ThanksgivingDay1998) <> 1 then
(
If ThanksgivingDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(ThanksgivingDay1999) <> 7 and dayofweek(ThanksgivingDay1999) <> 1 then
(
If ThanksgivingDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(ThanksgivingDay2000) <> 7 and dayofweek(ThanksgivingDay2000) <> 1 then
(
If ThanksgivingDay2000 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- DAY AFTER DAY ----

//1998
If dayofweek(DayAfterDay1998) <> 7 and dayofweek(DayAfterDay1998) <> 1 then
(
If DayAfterDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(DayAfterDay1999) <> 7 and dayofweek(DayAfterDay1999) <> 1 then
(
If DayAfterDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(DayAfterDay2000) <> 7 and dayofweek(DayAfterDay2000) <> 1 then
(
If DayAfterDay2000 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- CHRISTMAS ----

//1998
If dayofweek(ChristmasDay1998) <> 7 and dayofweek(ChristmasDay1998) <> 1 then
(
If ChristmasDay1998 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//1999
If dayofweek(ChristmasDay1999) <> 7 and dayofweek(ChristmasDay1999) <> 1 then
(
If ChristmasDay1999 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2000
If dayofweek(ChristmasDay2000) <> 7 and dayofweek(ChristmasDay2000) <> 1 then
(
If ChristmasDay2000 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;