Date calc-weekdays only

[crimmelcp]

To all:
Is there a way to calculate the difference between 2 dates but only calculate the weekdays.
example:
02/15/2001 - 02/01/2001 might be 14 days but only 10 work days
Thanks
Charlie Crimmel

[Doug Chanley]

RE: Date calc-weekdays only

Charlie,

Here is a sample script that you can alter or build on to count the weekdays
between 2 dates..... There may be a better way to accomplish this, but this
will work.


''XBasic
count=0
start={01/01/01}
start_date=start
j_start=jdate(start)
end={02/10/01}
j_end=jdate(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
next i
count=num-count
trace.writeln ("Weekdays between "+dtoc(start_date)+" & "+dtoc(end)+" are "+ltrim(str(count)))

[Raymond Lyons]

RE: Date calc-weekdays only

I'm sure Doug's script is fine, but for things like this I have always found it to be better to build a function.

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.

Ray 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