Calculating next n buisness day

[ShoeTaz]

I am in need of a formula that calculates due dates in buessines days. The start date would be the current date and the end date is from a field called {CusotmerOrders.DueDate}. I have used Ken's @WorkDay's formula to extract the number of business day's between the two dates, however, I am not sure how to add the business day total to the current day and exclude weekends. The report has a group that that displays: Due Today, Due in 1 Business Day, Due in 2 Business Days, etc... I am using CR 8.0. Thanks in advance!

 

[kenhamady]

This is going into my FAQ this weekend, but here is a formula that should work. It doesn't check for holidays, just weekends.


WhileReadingRecords; // WhilePrintingRecords in some cases
DateVar InD:= {YourDate} ;
NumberVar InN:= {Number of Days};
NumberVar Rem:= Remainder (InN,5);

DateVar OutD:=
Truncate (InN /5)*7+Ind +
If DayOfWeek(InD)-1 + REM > 5
then Rem + 2 else Rem
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[chelseatech]

Crystal Decisions have a UFL that you can download from their site that lets you cretae a list of public holidays and use those to calculate an accurate Businessdays function.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kenhamady]

Formula #17 in faq149-243 is based on the formula I started above, but it now includes holiday logic, and deals with periods that start on a weekend or holiday.

Chelseatech, I looked at the UFL and I thought it only calculated Business Days between two given dates. I didn't think it allowed you to specity a start date and a number of days. Did I miss something?
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SchuhTL]

Ken, I am using the current date as {Start Date} and your @WorkDays formula for the {Number of Day's}. If the future due date is 04/05/2002(Friday) the formula that you posted above will return 04/08/2002(Monday). If the due date is 04/06/2002(Saturday) the formula returns the correct day 04/08/2002. I have tried to edit your formula to
Local NumberVar InN:= {@WorkDays}-1;. This will return the correct due date if it falls on a weekday, however if the due date is 04/06/2002(Saturday) the formula will return the previous Friday not the following Monday. Any idea's what I need to change?

 

[kenhamady]

The Workdays formula assumes that you count both the beginning and end date. So a project that starts today and ends tomorrow requires 2 business days.

My "Add Business Days" formula does NOT count the start date. Adding 1 business day to today would give you tomorrow. The logic is not the same because they aren't intended to be used simultaneously. To use the first formula you have to know the end date, and wouldn't need the second formula.

Are you just testing the logic, or is there a reason that you need both of them at the same time? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com