How to calculate Business Days between 2 dates including a NULL field

[CappsRLO]

I have 2 fields that I am trying to calculate the difference between however one of the fields may be null for some records so I am using a 'if,then,else' formula as follows:
if isnull({Requests for Information.RFI~DateResponded}) then
{Requests for Information.RFI~DateCreated}-currentdatetime
else {Requests for Information.RFI~DateResponded}-{Requests for Information.RFI~DateCreated}

NOW the user wanted to calculate the difference in Business Days so I copied a formula from a previous post and replaced it with my fields however it won't let me use the 'else' part of my formula above.
Here is the new formula I am trying to use:

if isnull({Requests for Information.RFI~DateResponded}) then
//Subtracting Business Days:
//Main formula
WhileReadingRecords;
Local DateVar Start = {Requests for Information.RFI~DateCreated}; // place your Starting Date here
Local DateVar End = currentdatetime; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;

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

else

WhileReadingRecords;
Local DateVar Start = {Requests for Information.RFI~DateResponded}; // place your Starting Date here
Local DateVar End = {Requests for Information.RFI~DateCreated}; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;

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

HELP PLEASE!!!

 

[IanWaterman]

Replace you if then else with a date variable

local datevar defaultdate:= (if isnull({Requests for Information.RFI~DateResponded}) then
{Requests for Information.RFI~DateCreated}-currentdatetime
else {Requests for Information.RFI~DateResponded}-{Requests for Information.RFI~DateCreated});



//Subtracting Business Days:
//Main formula
WhileReadingRecords;
Local DateVar Start = defaultdate; // place your Starting Date here
Local DateVar End = currentdatetime; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;

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

Ian

 

[CappsRLO]

using this formula I receive the error 'A Date is Require Here' for the very first line:
<cursor blinks here>(if isnull({Requests for Information.RFI~DateResponded}) then

 

[lbass]

If it's a datetime, use datetimevar instead of datevar.

-LB

 

[CappsRLO]

The field {Requests for Information.RFI~DateResponded} is only a DATE field, not a DateTime field.
I cannot figure out why it is saying that a Date is required there when that field is a DATE field.
Only think I can think of is that this field can be NULL depending on the record.

 

[lbass]

Try using currentdate instead of currentdatetime.

-LB

 

[CappsRLO]

I tried that as well but still no luck.
Not sure if this matters but I am using Crystal 8.5..

 

[IanWaterman]

Try

local datevar defaultdate:= (if isnull(date({Requests for Information.RFI~DateResponded})) then
Date({Requests for Information.RFI~DateCreated})-currentdate
else date({Requests for Information.RFI~DateResponded})-Date({Requests for Information.RFI~DateCreated}));

Just forcing every record to a date

Ian