Modification to Business Days Calc 1

[tcgoth]

CR 8.0 on SQL

I am utilizing the Business Objects Support solution for calculating Business Days. As is formula works fine, however, I need to look at "LastDay" as a variable.

If "LastDay" is not null, perform the calculation, else use CurrentDateTime and perform the calculation.

Any ideas on how I can modify to accomodate this modification? Thanks!

//CALCULATE THE NUMBER OF BUSINESS DAYS
//BETWEEN FirstDay AND LastDay
//EXCLUDING HOLIDAYS FROM THE TOTAL
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED
//Replace date(2001,12,23) with your starting date
DateVar FirstDay:= date({rpt_customer.cust_rec_date});
//Replace date(2001,12,29) with your ending date
DateVar LastDay:= date({program.active_date});
//Holidays need to be assigned to all the holidays that
//need to be removed from the total
DateVar NewYearsDay2006:=Date(2006,01,02);
DateVar PresidentsDay2006:=Date(2006,02,20);
DateVar MemorialDay2006:=Date(2006,05,29);
DateVar IndependenceDay2006:=Date(2006,07,04);
DateVar LaborDay2006:=Date(2006,09,04);
DateVar ThanksgivingDay2006:=Date(2006,11,23);
DateVar DayAfterDay2006:=Date(2050,11,27);
DateVar ChristmasDay2006:=Date(2006,12,25);
DateVar NewYearsDay2007:=Date(2007,01,01);
DateVar PresidentsDay2007:=Date(2007,02,19);
DateVar MemorialDay2007:=Date(2007,05,28);
DateVar IndependenceDay2007:=Date(2007,07,04);
DateVar LaborDay2007:=Date(2007,09,03);
DateVar ThanksgivingDay2007:=Date(2007,11,22);
DateVar DayAfterDay2007:=Date(2050,11,26);
DateVar ChristmasDay2007:=Date(2007,12,25);
DateVar NewYearsDay2008:=Date(2008,01,01);
DateVar PresidentsDay2008:=Date(2008,02,18);
DateVar MemorialDay2008:=Date(2008,05,26);
DateVar IndependenceDay2008:=Date(2008,07,04);
DateVar LaborDay2008:=Date(2008,09,01);
DateVar ThanksgivingDay2008:=Date(2008,11,27);
DateVar DayAfterDay2008:=Date(2050,11,24);
DateVar ChristmasDay2008:=Date(2008,12,25);
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//END INPUT REQUIRED _ END INPUT REQUIRED _ END INPUT
//Other variables being used
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar Holidays:=0;
//BEGIN FORMULA:
//*********************************************************
//FINISH FORMULA IF FIRSTDAY OR LASTDAY IS NULL
//*********************************************************
IF FirstDay <=Date(0,0,0) or LastDay <=Date(0,0,0) then
Finaldays:= 0
//ELSE ASSIGN FINALDAYS
//*********************************************************
ELSE
(
//ASSIGN START DATE
//*********************************************************
//if the first day falls on a weekend, StartDate is equal
//to the following Monday for calculation reasons
If DayOfWeek(FirstDay) = 7 Then
StartDate := FirstDay + 2
Else If DayOfWeek(FirstDay) = 1 Then
StartDate := FirstDay + 1
Else
StartDate:=FirstDay;
//ASSIGN END DATE
//*********************************************************
//if the last day falls on a weekend, EndDate is equal to
//the following Monday for calculation reasons
If DayOfWeek(LastDay) = 7 Then
EndDate := LastDay + 2
Else If DayOfWeek(LastDay) = 1 Then
EndDate := LastDay + 1
Else
EndDate := LastDay;
//CALCULATE DAYS (including today First Day and Last Day)
//*********************************************************
Days:= (EndDate-StartDate) + 1;
//CALCULATE WEEKENDS
//*********************************************************
Weekends:= (EndDate - (dayofweek(EndDate)-1)) - (StartDate
+ (7-dayofweek(StartDate))) +1;
If Weekends = 2 then
Weekends:=Weekends
else If Weekends <=0 then
Weekends:=0
else
Weekends:= (((Weekends-2)/7)*2) + 2;
//CALCULATE HOLIDAYS
//*********************************************************
//---- NEW YEARS DAY ----
//2006
If dayofweek(NewYearsDay2006) <> 7 and
dayofweek(NewYearsDay2006) <> 1 then
(
If NewYearsDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(NewYearsDay2007) <> 7 and
dayofweek(NewYearsDay2007) <> 1 then
(
If NewYearsDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(NewYearsDay2008) <> 7 and
dayofweek(NewYearsDay2008) <> 1 then
(
If NewYearsDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//---- PRESIDENTS DAY ----
//2006
If dayofweek(PresidentsDay2006) <> 7 and
dayofweek(PresidentsDay2006) <> 1 then
(
If PresidentsDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(PresidentsDay2007) <> 7 and
dayofweek(PresidentsDay2007) <> 1 then
(
If PresidentsDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(PresidentsDay2008) <> 7 and
dayofweek(PresidentsDay2008) <> 1 then
(
If PresidentsDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//---- MEMORIAL DAY ----
//2006
If dayofweek(MemorialDay2006) <> 7 and
dayofweek(MemorialDay2006) <> 1 then
(
If MemorialDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(MemorialDay2007) <> 7 and
dayofweek(MemorialDay2007) <> 1 then
(
If MemorialDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(MemorialDay2008) <> 7 and
dayofweek(MemorialDay2008) <> 1 then
(
If MemorialDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//---- INDEPENDENCE DAY ----
//2006
If dayofweek(IndependenceDay2006) <> 7 and
dayofweek(IndependenceDay2006) <> 1 then
(
If IndependenceDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(IndependenceDay2007) <> 7 and
dayofweek(IndependenceDay2007) <> 1 then
(
If IndependenceDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(IndependenceDay2008) <> 7 and
dayofweek(IndependenceDay2008) <> 1 then
(
If IndependenceDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//---- LABOR DAY ----
//2006
If dayofweek(LaborDay2006) <> 7 and dayofweek(LaborDay2006)
<> 1 then
(
If LaborDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(LaborDay2007) <> 7 and dayofweek(LaborDay2007)
<> 1 then
(
If LaborDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(LaborDay2008) <> 7 and dayofweek(LaborDay2008)
<> 1 then
(
If LaborDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//---- THANKSGIVING ----
//2006
If dayofweek(ThanksgivingDay2006) <> 7 and
dayofweek(ThanksgivingDay2006) <> 1 then
(
If ThanksgivingDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(ThanksgivingDay2007) <> 7 and
dayofweek(ThanksgivingDay2007) <> 1 then
(
If ThanksgivingDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(ThanksgivingDay2008) <> 7 and
dayofweek(ThanksgivingDay2008) <> 1 then
(
If ThanksgivingDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//---- DAY AFTER DAY ----
//2006
If dayofweek(DayAfterDay2006) <> 7 and
dayofweek(DayAfterDay2006) <> 1 then
(
If DayAfterDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(DayAfterDay2007) <> 7 and
dayofweek(DayAfterDay2007) <> 1 then
(
If DayAfterDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(DayAfterDay2008) <> 7 and
dayofweek(DayAfterDay2008) <> 1 then
(
If DayAfterDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//---- CHRISTMAS ----
//2006
If dayofweek(ChristmasDay2006) <> 7 and
dayofweek(ChristmasDay2006) <> 1 then
(
If ChristmasDay2006 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2007
If dayofweek(ChristmasDay2007) <> 7 and
dayofweek(ChristmasDay2007) <> 1 then
(
If ChristmasDay2007 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2008
If dayofweek(ChristmasDay2008) <> 7 and
dayofweek(ChristmasDay2008) <> 1 then
(
If ChristmasDay2008 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//CALCULATE FINAL DAYS (DAYS - WEEKENDS - HOLIDAYS)
//*********************************************************
FinalDays:=Days-Weekends-Holidays;
//If the Last Day is on a weekend then
If DayOfWeek(LastDay) = 7 then
FinalDays:= FinalDays - 1;
If DayOfWeek(LastDay) = 1 then
FinalDays:= FinalDays - 2;
);
//DISPLAY NUMBER OF BUSINESS DAYS IN THE RANGE
//*********************************************************
FinalDays-1;

 

[synapsevampire]

Change the lastday line in the formula to:

DateVar LastDay;
if isnull(date({program.active_date})) then
LastDay:=currentdatetime
else
LastDay:={program.active_date};

-k

 

[tcgoth]

Thank you synapsevampire for the quick response.

When I use this solution I get a "Field Required Here" error message on the "Date" syntax of

if isnull(date({program.active_date}))

Thoughts on what might be causing this?

 

[synapsevampire]

Sorry, that should be:

if isnull({program.active_date}))... It's already a date.

-k