Calculate # of Days Between Dates but with Conditions

[mavsman]

I am trying to calculate number of days between dates which i have a formula for (I believe this is the Ken Hamady formula):

WhileReadingRecords;
Local DateVar Start := DATE({EVENT_DATTIM}); // place your Starting Date here
Local DateVar End := CurrentDate +1; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

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);

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 );

This works great but I need to skip days when one of the dates being calculated matches a condition in another field. One of the tables I'm using has a column 'Suspended Code'. If one of those dates meets one of the suspended code reasons then don't count that day in the calculation. I hope I explained this ok. Thanks in advance!

Weeks + Days - Hol

 

[Madawc]

You're doing the task with variables. In a formula field, Crystal would do a lot of it for you with DateDiff.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

This formula returns a difference between your event date and tomorrow, so what date are you potentially excluding? Is it a date in some other datefield that falls within the defined range? Or are you referring to the event date itself? If you mean the event date itself, just replace the event date in the above formula with a conditional formula that excludes irrelevant dates, like:

if {suspendedcode} in [123,344,343} then
date({@null}) else
{eventdate}

...where {@null} is a new formula that you open and save without entering anything.

-LB

 

[mavsman]

Yes, I am suspending dates within the range. If the event date starts on 08/01 and the end date is 08/05 (for this example counting everyday) that would give me 5 days. If 08/03 date in my table had a specific code next to that date then I would not count the 08/03 date. So instead of 5 days between dates I would only have 4 days.

 

[lbass]

Then change the formula to:

//{@otherdate}:
if {suspendedcode} in [123,344,343} and
{otherdate} >= {eventdate} and
{otherdate} < currentdate+2 then
1

Then you can use a second formula:

{@kenhamadyformula}-sum({@otherdate},{table.groupfield})

-LB