excluding non working days 2

[fishshop]

Crystal 8.5 - How do you build a query that calculates the difference (in days) between two date values but excludes weekends (saturdays/sundays?

 

[kskid]

See

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

Here's a version I found here and modified it to include a holiday table that I have.

Shared datevar SendDt;
shared datevar ReturnDt;
NumberVar Add:= 15; // put the number of days here to add (a positive number)
NumberVar Added := 0;
datevar target := dateserial(year(currentdate),month(currentdate)+1,1);

while dayofweek(target) in [1,7] or target = {CALENDAR_EXCEPTIONS.EXCEPTION_DATE}
do target = target + 1;
SendDt := target;


WHILE Added < Add 
Do (target := target +1;
    if dayofweek (target) in [2 to 6] and not (target = {CALENDAR_EXCEPTIONS.EXCEPTION_DATE}) 
        then Added:=Added+1
        else Added:=Added);
ReturnDt := Target;

 

[fishshop]

Thanks for the response - I found a time diff calc and have adapted that to do what I wanted - users just have to put their two date fields in in place of {ENTER DATE FIELD 1&2}:

**********************************************
//TO CALCULATE THE NUMBER OF WORKING DAYS
//BETWEEN two dates (FirstDateTime AND LastDateTime)
//(INCLUDES BANK HOLIDAYS ETC IN THE TOTAL)

//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//INPUT REQUIRED _ PLACE YOUR TWO DATE FIELDS BELOW

//Set your starting //date field
DatetimeVar FirstDateTime:= {ENTER DATE FIELD 1 HERE};
//Set your ending //date field
DatetimeVar LastDateTime:= {ENTER DATE FIELD 2 HERE};

//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//END INPUT REQUIRED _ END INPUT REQUIRED _ END INPUT

//Other variables used in this formula
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;

//BEGIN FORMULA:
//*********************************************************

//END FORMULA IF FirstDateTime OR LastDateTime IS NULL
//*********************************************************
IF FirstDateTime <=Date(0,0,0) or LastDateTime <=Date(0,0,0) then days:= 0

//CALCULATE THE DATES
//*********************************************************
ELSE
(

//ASSIGN STARTDATE and ENDDATE
//*********************************************************
//if the first day falls on a weekend, set the StartDate to equal the following Monday for calculation reasons
If DayOfWeek(FirstDateTime) = 7 Then
StartDate := date(FirstDateTime) + 2
Else If DayOfWeek(FirstDateTime) = 1 Then
StartDate := date(FirstDateTime) + 1
Else StartDate:=date(FirstDateTime);

//if the last day falls on a weekend, set the EndDate to equal the following Monday for calculation reasons
If DayOfWeek(LastDateTime) = 7 Then
EndDate := date(LastDateTime) + 2
Else If DayOfWeek(LastDateTime) = 1 Then
EndDate := date(LastDateTime) + 1
Else EndDate := date(LastDateTime);

//CALCULATE THE WEEKSDAYS AND WEEKENDS
//*********************************************************
//Calculate Days (including First day and Last day)
Days:= (EndDate - StartDate)+1;

//Calculate any weekends
if Days >= 7 then
WeekEnds := (Round((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else WeekEnds := 0;

//CALCULATE THE FINAL DAYS BY REMOVING WEEKENDS
//*********************************************************
//If the Last Day is on a weekend then FinalDays subtract the weekend days
If DayOfWeek(LastDateTime) = 7 then FinalDays:= FinalDays - 1;
If DayOfWeek(LastDateTime) = 1 then FinalDays:= FinalDays - 2;

//Assign FinalDays to Days minus Weekends
FinalDays:= Days - WeekEnds;
);

******************
Seems to work well