Show the nuber of work days excluding week ends 5

[barrattp]

Hello,
I an trying to create a formula that will give me the number of work days between two dates excluding the week end. I have been using the attached formula that I got out of Crystal Decision. It calculates the number of days BUT does not exclude the weekends. Eg if the start date is Fiday and the end date is Monday it shows 3 days rather than 1.

//CALCULATE THE NUMBER OF BUSINESS DAYS
//INCLUDING HOLIDAYS IN THE TOTAL
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED
//ENTER FIRST DATE
DateVar FirstDay:= Date({WORKORDER.ACTSTART});
//ENTER END DATE
DateVar LastDay:= Date({WORKORDER.ACTFINISH});
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//END INPUT REQUIRED _ END INPUT REQUIRED _ END INPUT
//Other variables used in the formula
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
//BEGIN FORMULA:
//*********************************************************
//FINISH FORMULA IF FIRSTDAY OR LASTDAY IS NULL
//*********************************************************
IF FirstDay <=Date(0,0,0) or LastDay <=Date(0,0,0) then
Finaldays:= 0
//ELSE ASSIGN FINALDAYS
//*********************************************************
ELSE
(
//ASSIGN START DATE
//*********************************************************
//if the first day falls on a weekend, StartDate is equal
//the following Monday for calculation reasons
If DayOfWeek(FirstDay) = 7 Then
StartDate := FirstDay + 2
Else If DayOfWeek(FirstDay) = 1 Then
StartDate := FirstDay + 1
Else
StartDate:=FirstDay;
//ASSIGN END DATE
//*********************************************************
//if the last day falls on a weekend, EndDate is equal to
//the following Monday for calculation reasons
If DayOfWeek(LastDay) = 7 Then
EndDate := LastDay - 1
Else If DayOfWeek(LastDay) = 1 Then
EndDate := LastDay - 2
Else
EndDate := LastDay;
//CALCULATE DAYS (including today, First Day and Last Day)
//*********************************************************
Days:= (EndDate - StartDate) ;
//*********************************************************
Weekends:= (EndDate - (dayofweek(EndDate)-1)) - (StartDate
+ (7-dayofweek(StartDate))) +1;
if Weekends = 2 then
Weekends:=Weekends
else if Weekends <=0 then
Weekends:=0
else
Weekends:= (((Weekends-2)/7)*2) + 2;

//CALCULATE FINAL DAYS (DAYS - WEEKENDS)
//*********************************************************
FinalDays:=Days-Weekends;
if FinalDays < 0 then
FinalDays:=0;
);
//DISPLAY NUMBER OF BUSINESS DAYS IN THE RANGE
//*********************************************************
FinalDays;days

 

[dgillz]

Here it is, compliments of my friend Ken Hamady:

http://www.kenhamady.com/form01.html

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[mbarron]

Here are two other formula options, the first is a example formula from Cystal, with an addition. The addition is in bold. The original didn't calculate the first day of the range.

=================================================
datevar fd;
datevar ld;

local numbervar week;

fd:={startdate};
ld:={enddate};


numbervar week ;
week:=DateDiff (&quot;d&quot;, fd, ld) -
DateDiff (&quot;ww&quot;, fd, ld, crSaturday) -
DateDiff (&quot;ww&quot;, fd, ld, crSunday);

if dayofweek(fd) in [2,3,4,5,6] then week:=week+1;
week
========================================================

This formula is one of my own design. It will work in CR8 and up.
==========================================================
datevar firstday;
datevar lastday;
numbervar loop;
local numbervar wds;
numbervar span;
firstday:={startdate};
lastday:={enddate};


span:=lastday-firstday;
For loop:= 0 to span do(
if dayofweek(firstday+loop)in [2 to 6] then wds:=wds+1 else wds:=wds);

wds
========================================================

If you want to calculate the holidays, use the method as shown in Ken's formula.



Mike

 

[synapsevampire]

If possible, take a more long term professional approach by creating a Periods table.

It's standard fare in Data Warehousing, and should be a part of every reporting environment.

This table generally includes numerous attributes about every date, such as holiday, weekend, fiscal year, etc.

There are plenty of examples on the web, and it more efficiently solves these types of problems.

-k