Work Day Formula

[bld21]

I have used the formula below successfully to find the number of work days between 2 date fields in a different report.
I am now trying to modify a report which is using a command expression for a beginning and end date. When the report normally runs it will prompt for beginning and ending date. If I use the formula below I get an error with the line "Local DateVar Start:= date({Command.Expr1002});" with "Date Time is required here" for command.expr1002. Any ideas of how to modify this to accept the command.expr1002 date and the command term date??


//{maturity_billing.billing_dte} - account_termination.term_dte}

if {Command.Expr1002} - {Command.term_dte} < 0 then 0
else

WhileReadingRecords;
Local DateVar Start:= date({Command.Expr1002});
Local DateVar End:= date({Command.term_dte});
Local Numbervar Days;
Local Numbervar Weeks;
Local Numbervar Hol;
Datevar Array Holidays;

Weeks:=(Truncate(End-dayofWeek(End) + 1
-(Start - dayofWeek(Start) + 1))/7)*5;
Days:=DayOfWeek(End)-DayOfWeek(Start) +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i:=1 to Count(Holidays)
do (if DayOfWeek( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1);

Weeks + Days - Hol


Thanks everyone!!

 

[lbass]

Sounds like the expression might already be a date. Try removing the date() around it. If that doesn't work, then the expression is not a date or a datetime, and you should report back with its datatype.

-LB

 

[bld21]

Thanks LB, that worked.

BD

 

[bld21]

The below formula works except for the if statement. See below. This does not return 0 but a negative number. Example: term dt = 3/20/2008 and expr1002 date = 3/18/2008 it will return a -2.00 and not 0. I have tried everything...Any ideas?


If {Command.term_dte} > {Command.Expr1002} then 0 else

WhileReadingRecords;
Local DateVar Start:= {Command.term_dte};
Local DateVar End:= {Command.Expr1002};
Local Numbervar Days;
Local Numbervar Weeks;
Local Numbervar Hol;
Datevar Array Holidays;

Weeks:=(Truncate(End-dayofWeek(End) + 1
-(Start - dayofWeek(Start) + 1))/7)*5;
Days:=DayOfWeek(End)-DayOfWeek(Start) +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i:=1 to Count(Holidays)
do (if DayOfWeek( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1);

Weeks + Days - Hol;

 

[lbass]

Try something like:

WhileReadingRecords;
Local DateVar Start:= {Command.term_dte};
Local DateVar End:= {Command.Expr1002};
Local Numbervar Days;
Local Numbervar Weeks;
Local Numbervar Hol;
Datevar Array Holidays;

If {Command.term_dte} > {Command.Expr1002} then (
Weeks := 0;
Days := 0;
Hol := 0
);
If {Command.term_dte} <= {Command.Expr1002} then (
Weeks:=(Truncate(End-dayofWeek(End) + 1
-(Start - dayofWeek(Start) + 1))/7)*5;
Days:=DayOfWeek(End)-DayOfWeek(Start) +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0)
);

Local NumberVar i;
For i:=1 to Count(Holidays)
do (if DayOfWeek( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1);

Weeks + Days - Hol;

-LB

 

[bld21]

Awesome!