I am having problems with this business days formulas...How can I modify this formula to give me an accurate number of days. Right now this formula, for example, if a ticket age is actually .5 days open it would give me 1 day. If it was 1.5 days open it would give me 2 days. Even when attempting to format the text, and I try to round it..it's just giving me whole numbers for example 1.000, 2.00, 3.0..etc. I found this formula off of the crystal decisions website. Any help would be greatly appreciated.
The formula is below:
//CALCULATE THE NUMBER OF BUSINESS HOURS
//BETWEEN FirstDateTime AND LastDateTime
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED
//Replace datetime(2001,12,23,11,50,0) with your starting //date
DatetimeVar FirstDateTime:= {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
if {BSCC-FL-TM&ACTION_JOIN.Call_Status} = 'PENDING'
then DatetimeVar LastDateTime:= {BSCC-FL-TM&ACTION_JOIN.Status-History.PENDING.TIME};
if {BSCC-FL-TM&ACTION_JOIN.Call_Status} = 'OPEN'
then DatetimeVar LastDateTime:= currentdate;
//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;
)
The formula is below:
//CALCULATE THE NUMBER OF BUSINESS HOURS
//BETWEEN FirstDateTime AND LastDateTime
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED
//Replace datetime(2001,12,23,11,50,0) with your starting //date
DatetimeVar FirstDateTime:= {BSCC-FL-TM&ACTION_JOIN.Call_Time Call Received};
//Replace datetime(2001,12,26,10,0,0) with your ending //date
if {BSCC-FL-TM&ACTION_JOIN.Call_Status} = 'PENDING'
then DatetimeVar LastDateTime:= {BSCC-FL-TM&ACTION_JOIN.Status-History.PENDING.TIME};
if {BSCC-FL-TM&ACTION_JOIN.Call_Status} = 'OPEN'
then DatetimeVar LastDateTime:= currentdate;
//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;
)