Count business days

[rmiller3455]

I have a report with the following fields

"Order_Date"
"Service_Date"
"response_time" (a calculate field that is equal to "Service_Date-Order_Date").

The response_time field should only count business days, not weekends. Does anyone know how to do this?

Thanks

Rick

[Stephen Williams]

RE: Count business days

Rick-

I suggest you make a user-defined function

function BusinessDays as N(FromDate as D, ToDate as D)
' you are going to have to figure a short script to do the math
' I mean, what IS a business day? Mon - Fri? July 4th? Xmas? Hannukah?
BusinessDays = result
end function

Then where you need it, you simply put
response_time = BusinessDays(Order_Date,Service_Date)

[Tom Cone Jr]

RE: Count business days

Stephen, I think it's apt to be a lengthy script. You'll need something like this:


pseudo code

'count days from start to finish, skipping weekends and holidays
currentdate = begindate
while currentdate

[Raymond Lyons]

RE: Count business days

Do you want to exclude just weekends or weekends and holidays? The latter is a bit more complicated as it requires a table for holidays that the function has to access. Peter Wayne's Xbasic for Everyone has a section on this topic. You could get a function from there, or if you let me know which you want I can give you a variant of what Peter did in his book.

Raymond Lyons

[Raymond Lyons]

RE: Count business days

Actually I already provided the functions on this board in Msg ID 34265. Search for that.

Raymond Lyons

[crimmelcp]

RE: Count business days

Rick:
Here is a sample script that I use to count weekdays between 2 dates.
It will give you the type of code and syntax to use.
You may be able to modify the fields to use with your data.
-------------------------------------------
'Here is a sample script that you can alter or build on to count the weekdays
'between 2 dates..

trace.clear()
dim global count as n
dim global count1 as n
dim global start as d
dim global end as d
dim global start_date as d
dim global j_start as n
dim global j_end as n
count=0
count1=0
var->start=ctod("01/01/2001")
trace.writeln ("start -"+dtoc(var->start))
var->start_date=start
trace.writeln ("start_date -"+dtoc(var->start_date))
var->j_start=jdate(start)
trace.writeln ("j_start -"+str(var->j_start))
var->end=ctod("02/10/2001")
trace.writeln ("end -"+dtoc(var->end))
var->j_end=jdate(end)
trace.writeln ("j_end -"+str(var->j_end))
num=j_end-j_start
for i=1 to num
start=start+1
'trace.writeln(str(dow(start)))
if dow(start)=1 .or.dow(start)=7
count=count+1
end if
if dow(var->start) 1 .and. dow(var->start) 7
trace.writeln("date is -"+dtoc(start))
end if
next i
count=num-count
trace.writeln ("Weekdays between "+dtoc(start_date)+" & "+dtoc(end)+" are "+ltrim(str(count)))

[rmiller3455]

RE: Count business days

Thanks for everyone's input, I found some information in the "Xbasic for Everyone" book and I must admit, now I'm really confused. Where does this script go? Do I include in the field rules, the report, etc? I've gone through the book several times, and honestly, can not find out how to use a script to update a field. Any I looking to hard for the answer?

Thanks all,

Rick

[Raymond Lyons]

RE: Count business days

Rick,

I guess you need to bone up on user defined functions in the manuals. Peter's script (and my variants of it below) are functions. You create functions in the Control Panel, Code, Functions. For my weekdays functions below you would create a weekdays function as a numerical function with two date parameters (Start_Date and End_Date). Then you could just paste in my code below or type in Peter's code or whatever code you chose to use. From that point on you could use it just like Alpha's built-in functions. It would even show up as an available function in the Expression Builder. That means it could be used in calculated field on a report or even as a calculated field in a field rule. But before you do one of these functions you might want to create the simple one that appears earlier in Peter's book, just to get used creating and using these functions.

Below is from my earlier post in Msg ID ??(whatever I said it was above).
Raymond Lyons

*******************
The following Weekdays and Workdays functions are based on similar functions in Peter Wayne's Xbasic for Everyone book. The Weekdays function has not been changed much, but the comments are mine, and they may or may not accurately reflect what Peter is doing in his code. For holidays, my app had to deal with long school holiday periods, so instead of requiring that every single day off work ("holiday") date be in a table called Holidays, I changed the table and the function to allow for holiday periods (date ranges) and used my separate Workdays function in a calculated table field "Days" to determine the number of "holidays" (days off work) in the period.

Raymond Lyons

function Weekdays as N(Start_Date as D,End_Date as D)
dim dow1 as n
dim dow2 as n
dim weekends as n
dim offset as n
If End_Date={}
End_Date={01/01/0001}
End If
If Start_Date={}
Start_Date={01/01/0001}
End If

'**Going to divide by 7 to get weekends, so have to get full weekends in the range
'** Use offsets to do this.
'**Each offset below either adds or subtracts weekend days to the date
'**in the date field. Then just /7 to get the # of weekends in the offset range

dow1=dow(Start_Date) 'Day of week for the beginning of the date range
offset=case(dow1=7,2,dow1=1,1,.t.,0) '**7=sat, 1=sun
Start_Date=Start_Date+offset
dow2=dow(End_Date) '**Day of week for the last day of the date range
offset=case(dow2=7,-1,dow2=1,-2,.t.,0) '**7=sat, 1=sun
End_Date=End_Date+offset

'**Now just calculate # of weekends and add a fudge factor in the event,
'**that first - last goes, say, from a Thurs to a Monday

weekends=int((End_Date-Start_Date)/7)+iif(dow(Start_Date)>dow(End_Date),1,0)
weekdays=End_Date-Start_Date+1-2*weekends
end function

�********************
The function below uses a 4 field holidays table:
Hol_Start Date 8 0
Back_On Date 8 0
Name_Of_Holiday Char 25 0
Days Numeric 3 0
The Days field above is a calculated field:
WEEKDAYS(HOL_START,IF(BACK_ON={},HOL_START,BACK_ON))
Also note that I use Back_On to mean the date one comes back to work, not the last day off work in the period. With adjustments you could do it the other way.

function Workdays as N(First as D,Last as D,Name_of_Hol_Tbl as C)
dim dow1 as n
dim dow2 as n
dim weekends as n
dim nholidays as n
dim offset as n
dim hol as p 'pointer to the holidays table
dim ip as p
dim query.description as c
dim query.filter as c
dim query.options as c
dim query.order as c
dim fld as p 'pointer to the field in holidays
dim field_name as c
dim indx as p
If Last={}
Last={01/01/0001}
End If
If First={}
First={01/01/0001}
End If
on error goto no_holiday_table
hol=table.open(Name_of_Hol_tbl)
on error goto 0

fld=hol.field_get(1) '**Pointer to 1st field in the file
if fld.type_get()"D" then '**Make sure the 1st field is a date
workdays=-1
exit function
end if
'**above was just to be safe
field_name=fld.name_get() '**name of the 1st field-will be used below

'**1st, divide by 7 to get weekends, so have to get full weekends in the range
'** Use offsets to do this. (NOTHING HERE TO DO WITH HOLIDAYS TABLE)
'**Each offset below either adds or subtracts weekend days to the date
'**in the date field. Then just /7 to get the # of weekends in the offset range

dow1=dow(first) '**Day of week for the beginning of the date range
offset=case(dow1=7,2,dow1=1,1,.t.,0) '7=sat, 1=sun
first=first+offset
dow2=dow(last) '**Day of week for the last day of the date range
offset=case(dow2=7,-1,dow2=1,-2,.t.,0) '7=sat, 1=sun
last=last+offset
'**Now just calculate # of weekends and add a fudge factor in the event,
'**that first - last goes, say, from a Thurs to a Monday

weekends=int((last-first)/7)+iif(dow(first)>dow(last),1,0)

'**NOW DO TO HOLIDAYS***

dim Holidays_count as n
Holidays_count=0
hol.index_primary_put("Hol_Start")
hol.fetch_first()
WHILE .not. hol.fetch_eof()
IF BETWEEN(hol.Hol_Start,first,last)
Holidays_count=Holidays_count+hol.days
END IF
hol.fetch_next()
END WHILE

'**Finally, just calculate the workdays

workdays=last-first+1-Holidays_count-2*weekends
hol.close()
exit function

no_holiday_table:
on error goto 0
workdays=-1
exit function
end function

[rmiller3455]

RE: Count business days

Thanks for all your help Raymond and all. I finally got this figured out this morning, could not have done it with all of your help!

Thanks again,

Rick