The formula below calculates my company's business hours (M-F, 7:00 AM-5:00 PM EST) Does anyone know the formula to exclude specific Holidays?
This is the formula:
//Determine how many Business Hours
//are within a date range
//WhilePrintingRecords;
//example (Y,M,D,H,M,S): datetime(2000,04,17,8,0,0)
DatetimeVar FDay:=
{@SubmitDate};
//example (Y,M,D,H,M,S):Datetime(2000,04,18,17,0,0)
DatetimeVar LDay:=
{@LastResolvedDate}; TimeVar
//example for 7:30 AM: time(7,30,0)
BusinessStartTime:= time(7,0,0);
//example for 5:00 PM: time(17,0,0)
TimeVar BusinessEndTime:= time(16,59,59);
NumberVar BSTime;
NumberVar BETime;
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar halfdays;
NumberVar fulldays;
NumberVar hours;
//If first day or last day is null or less than null assign
//hours to 0 and finish the formula
if FDay <=Date(0,0,0) or LDay <=Date(0,0,0) then
hours:= 0
//Else assign hours
else
(
//Assign Business Start Time and Business End Time
BSTime:= hour(BusinessStartTime) +
(minute(BusinessStartTime) / 60);
BETime:= hour(BusinessEndTime) + (minute(BusinessEndTime) /
60);
//Assign First Day and Last Day
//Determine whether FDay falls within
//start time and end time
if hour(FDay) in BSTime to BETime then
FDay:= FDay
else if hour(FDay) > BETime then
FDay:= datetime(date(FDay)+1, BusinessStartTime)
else if hour(FDay) < BSTime then
//FDay:= datetime(date(FDay), BusinessStartTime);
FDay:= datetime(date(FDay), BusinessStartTime);
//Determine whether LDay falls within start
//time and end time
if hour(LDay) in BSTime to BETime then
LDay:= LDay
//else if hour(LDay)>BETime and hour(FDay)>BETime then
//LDay:= datetime(date(LDay), BusinessStartTime)
else if hour(LDay) > BETime then
LDay:= datetime(date(LDay), BusinessEndTime)
//LDay:= datetime(date(LDay), BusinessStartTime)
else if hour(LDay) < BSTime then
//The following accounts for tickets closed on Mondays between 12:01am and 7:00am
If DayOfWeek(LDay) = 2 Then
LDay:= datetime(date(LDay)-1, BusinessStartTime)
else
//Monday 12-7
LDay:= datetime(date(LDay)-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(FDay) = 7 Then
StartDate := date(FDay) + 2
Else
If DayOfWeek(FDay) = 1 Then
StartDate := date(FDay) + 1
Else
StartDate:=date(FDay);
//if the last day falls on a weekend,
//EndDate is equal to the following Monday
//for calculation reasons
If DayOfWeek(LDay) = 7 Then
EndDate := date(LDay) + 2
Else If DayOfWeek(LDay) = 1 Then
EndDate := date(LDay) + 1
Else
EndDate := date(LDay);
//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(LDay) = 7 then
FinalDays:= FinalDays - 1;
If DayOfWeek(LDay) = 1 then
FinalDays:= FinalDays - 2;
//Assign FinalDays to Days - Weekends
FinalDays:= Days - WeekEnds;
//Calculate Hours
//If there is less than 2 days, hours = last
//days hour - first days hour
If FinalDays <= 1 and FinalDays <> -2 then
(if hour(LDay) <= BETime then
hours:= (time(LDay) - time(FDay))/3600
//else if FinalDays =-2 then
//hours:= FinalDays
else if FinalDays = -2 then
hours:=0
else
//hours:= (0 - 0)/3600)
hours:= (BusinessEndTime - time(FDay))/3600)
//Else hours = how many hours on the two half
//days + how many hours for the full days
Else
(
halfdays:= ((BusinessEndTime - time(FDay)) /3600 +
(time(LDay) - BusinessStartTime)
/3600);
fulldays:= (FinalDays-2) * 10;
hours:= halfdays + fulldays;
//hours:=0;
);
);
//Display Hours
//Display the number of hours between
//the business day ranges
if hours < 0 then
hours:= 0;
//FinalDays;
if {HPD_Help_Desk.Priority} <=1 then
//hours:=(((FDay-LDay) * -1)*24);
hours:=((({@SubmitDate}-{@LastResolvedDate}) * -1)*24);
//else
hours;
//Datetime(date(FDay));
//halfdays + Fulldays;
//{HPD_Help_Desk.Priority}