calculating business days in a range 1

[sabrina30]

I am working with Crystal Client 7, if that helps.

I need a formula to count the business days between two dates. I currently have this formula to count the days, but boss wants it more specific to just show count of working days. M-F : if {T_TMPLT.TMPLT_NUM}=1.00 then CurrentDate - {T_CALL_FORM.ENTRY_DT} else {T_CALL.RSLV_DT} - {T_CALL_FORM.ENTRY_DT}


I could probably get rid of the if statement and just count the business days between entry date and CurrentDate, but still need formula to count business days.

Thank you,

Sabrina

 

[MalcolmW]

What about stat holidays? (Usually a stat holiday is not considered a business day, although it is usually a weekday).

 

[sabrina30]

I got this formula for another answer:

WhilePrintingRecords;
DateVar Start := {table.StartDate}; //Replace this field with your Starting Date field
DateVar End := {table.EndDate}; //Replace this field with your Ending Date field
NumberVar Weeks;
NumberVar Days;
NumberVar Hol:= 0;


//Figure the number of Calendar "Rows" involved and count 5 days for each:
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

//Adjust the first and last weeks based on when in the week you start and end
Days := DayOfWeek(End) - DayOfWeek(Start) + 1
+ (if DayOfWeek(Start) = 1 then -1 else 0) //adjust for starting on a Sunday:
+ (if DayOfWeek(End) = 7 then -1 else 0); //adjust for ending on a Saturday:

//Adjust for Holidays in the period between the start and end dates:
if Date(1999,01,01) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,01,18) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,02,16) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,05,31) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,09,06) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,11,25) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,12,24) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,03) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,17) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,02,14) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,05,29) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,09,04) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,11,23) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,12,25) in start to end then Hol:= Hol+1 else Hol:= Hol;

//Assemble the adjusted work days
Weeks + Days - Hol

It works, but I need to have an if statement. If pending begin date to currentdate. If not pending Begin date to end date. Cann't get an if statement to work. Was told to try creating the if statement in another formula and putting it into the one above. Problem: I don't know how to do that. I can do the if statement in another formula no problem, but don't know how to add to above formula

 

[sabrina30]

I also need a way to be able to have a summary of average days out. Can't summarize with this formula. Thought someone might have a work around for me.

Sabrina

 

[kenhamady]

Sabrina,

I wrote the formula so maybe I can help out.

If you need to do a summary of this (like average) change the first line from WhilePrintingRecords to WhileReadingRecords. Summaries happen between reading and printing.

Now the if statement. You need to have it use one of two different fields for the ending date, based on a condition. This formula asks for a single end date field. But that end date field could be another formula that says:

If IsNull({endDate})
then currentdate
else {endDate}

Now this formula results in the correct date to use as an end date. Use the name of this formula as the end date field in the 3rd line of the main formula.

Let me know if it works. Ken Hamady

http://www.kenhamady.com/

 

[MalcolmW]

Just because it is now November 2000, and this formula (as written) will run out of stats in less than two months, you should probably consider having the stat holidays in a table - that way, all you have to do is periodically add some rows in one table, rather than edit this formula in all copies of all reports that use a stat holiday counting feature.
Although it may not be an issue for you, but stat holidays will vary by country and often by region within countries.

I know this doesn't help with your immediate problem, but it will cut down report maintenance.

 

[kenhamady]

The holidays in the formula are old examples. In the instructions I describe that since everyone has there own list it is expected that you will put in your own holidays. I should have given the instructions here.

The list can be as long as you like, going forward several years. However Malcolm is right, any changes must be made in each report. Ken Hamady

http://www.kenhamady.com/

 

[sabrina30]

Thank you everyone for your help. Ken, the formula works great now..