Calculating the number of business days between two dates 1

[RWWAMS]

Hi everyone, (Crystal Reports 8.5, SQL 2000)

I'm looking for a little advice. I've got an ugly formula that calculates the number of business days between two dates and times (I'm getting the start and end dates and times from a parameter). The formula excludes weekends and also factors in how much of the business day is included in the date range (assuming the business day starts at 9am and ends at 5pm).

I need some help expanding this formula to exclude company holiday's as well.

I mentioned above that I was looking for some advice. I'm not wed to the approach I'm using now. I'd actually prefer to have an external file or database table to hold the holidays (that way I don't have to change formulas every year).

If anyone has a technique that can accomplish all of the above . . . I'm all ears.

Thanks!

 

[synapsevampire]

The standard in data warehousing is to create a Period table.

This table has more than just Holidays, it has all dates, with attributes for all dates, such as:

Business day/Holiday/Weekend
Date
Day
Work Week
Month
Quarter
Year
Fiscal for each, etc.

In your case you could leverage the Business Day and Holiday attributes, and as you go forward, you could leverage the oterh attributes.

It's also a simple means to provide summary reports for all dates in a period where your data does not have data for some dates, just select from this table and join your data to it.

-k

 

[RWWAMS]

Thanks for your expert advice. I'll see if I can get this shaped up tonight.

Thanks again.

 

[RWWAMS]

I've got the table designed and populated, but I'm a little confused how this new table should be joined with my data and how the formula calculates business days.

Any additional direction would be very much appreciated.

Thanks

 

[rosalindr]

What you really want is a shop calendar. A shop calendar is an annual calendar, with all dates from 1/1/2004 to 12/31/2004. That would include 2/29/2004, since 2004 is a leap year. So you would have 366 records for 2004. You then need a flag to identify each date as a work day, a holiday, a saturday, or sunday. You could also have shifts, i.e. 7 am to 4 pm, 3 pm to 12 am, 11 pm to 8 am (one hr for lunch per shift). Each shift usually represents one work day.

You can join the calendar date field to any date field in your database. For example where I used to work, if you placed a purchase order with a due date that fell on a weekend or holiday, the system would give you a warning message and offer the option of setting the date to the next work day. In a manufacturing environment, a shop calendar for every year is manditory.

Roz

http://www.mytrainingdept.com

 

[Reebo99]

I agree that the best approach is to have a period table, as advised by SV.

However, some people don't have the luxury of being able to add tables. Below is the formula I use :

WhilePrintingRecords;
//Set the values of Start Date and End Date
DateVar StartDate := Date(2003,01,01);
DateVar EndDate := Date(2003,12,31);

//Find out the difference in days and subtract the weekends
NumberVar DaysDiff := DateDiff("d",StartDate,EndDate) -
DateDiff("ww",StartDate,EndDate,crsaturday) -
DateDiff("ww",StartDate,EndDate,crsunday);

//Create an array of Holiday dates
Local DateVar Array Holidays := MakeArray(
Date(2003,01,01),
Date(2003,01,02),
Date(2003,12,25),
Date(2003,12,26),
Date(2003,07,07));

//Loop through the array checking if each holiday is within the dates
Numbervar Counter := 0;
While UBound(Holidays) <> Counter do
(Counter := Counter + 1;
if Not(dayofweek(Holidays[Counter]) in [1,7]) and
Holidays[Counter] in StartDate to EndDate then DaysDiff := DaysDiff -1;

//Display result to 0 decimal places and no thousand separator
totext(DaysDiff,0,&quot;&quot;

Just replace the items in bold with you start and end date fields and the items in italics with your holiday dates.

This does have the downfall mentioned earlier of having to update the holidays each year.......

Reebo
UK

 

[RWWAMS]

I've joined the new Calendar Table to my data table using the calendar date field. How can I calculate the number of business days between the start and end dates using this new table? K suggested that I select from the new calendar table. Unfortunately this is not an option in our situation because some over-achieving employees may come in over the holiday and enter data (which will need to be retrieved).

Thanks for your help.

 

[biobrian]

I am having a problem with the formula mentioned above.

The start and end dates are selected before the report is run and change all the time. I made a formula to calculate the last date in my date range:

ToText ( Maximum ( {?Date Range} ) )

When I try and put the result of this formula into the formula mentioned above, it tells me a date is required. Any suggestions?

 

[biobrian]

After looking at this longer I am going about this the wrong way. I need to take the Required date and the recieve date to figure out days early/late. I then need to remove any Saturday's or Sundays that fall into the date range paramater.