>I'm looking for a function to calculate the number of work days; Monday - Friday, between two dates. I have an application that must do tons of these calculations so speed is quite important. ie - no looping through the date range to count the work days. My initial thoughts are:
> get the number of days between the dates (nworkdays)
> determine the start and end DOW
> reduce nworkdays by work days left in start week
> reduce nworkdays by number of days into end week
> determine number of weeks in remaining days to get weekend days (nworkdays/7)*2
> subtract weekend days from original nworkdays
>
>Any thoughts on better approaches?
>Better yet, anyone got such a function already written they would like to share.
>
>TIA
Michael,
I can't resist this.< g > Here's my approach, but it's old. It was originally designed for a FPW 2.6 system that is now defunct (that should explain the format for everyone).
PARAMETERS p_start, p_stop
PRIVATE m.days, m.stop, m.num_weeks
m.days = (p_stop - p_start) + 1
m.num_weeks = INT(m.days / 7)
m.result = m.num_weeks * 5
m.add_days = m.days % 7
m.stop = p_stop - m.add_days
DO WHILE m.stop < p_stop
m.stop = m.stop + 1
IF NOT INLIST(DOW(m.stop), 1, 7)
m.result = m.result + 1
ENDIF
ENDDO
RETURN m.result
Marcia, however, does have a good idea about the Holidays table. To incorporate that into the above, I'd simply use a COUNT TO m.holdays FOR BETWEEN(holi_date, p_start, p_stop) (where holi_date is the name of the date field holding the holiday date) and subtract the value in m.holidays.
George
Ubi caritas et amor, deus ibi est