Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: exclude holiday formula Post Reply Post New Topic
Author Message
tctoledo
Newbie
Newbie
Avatar

Joined: 08 Jun 2011
Location: United States
Online Status: Offline
Posts: 1
Quote tctoledo Replybullet Topic: exclude holiday formula
    Posted: 08 Jun 2011 at 11:13am

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}

tct
IP IP Logged
Chris_FL
Newbie
Newbie


Joined: 12 Oct 2011
Location: United States
Online Status: Offline
Posts: 1
Quote Chris_FL Replybullet Posted: 12 Oct 2011 at 9:05am
Hi,

You can find a formula with that information below. It's a white paper that calculates days and hours with and without holidays. It will take some tweaking to make it work in your environment, but it's a great jumpstart.

http://www.sdn.sap.com/irj/boc/go/portal/prtroot/docs/library/uuid/701a52c3-6b1e-2b10-21b3-a6e101be1a0f?QuickLink=index&overridelayout=true

Good luck.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.