Try again...
Oh, yeah, date math was my thing. From
https://support.office.com/en-us/article/NETWORKDAYS-function-48E717BF-A7A3-...
Is the spec. For VFP, we'll use an array of holidays range than Excel ranges...
BONUS: Validated against http://www.workingdays.us/
* Test Work days * duplicate Excel function NETWORKDAYS * NETWORKDAYS(start_date, end_date, [holidays]) * The NETWORKDAYS function syntax has the following arguments: * Start_date Required. A date that represents the start date. * End_date Required. A date that represents the end date. * Holidays Optional. An optional range of one or more dates to * exclude from the working calendar, such as state and federal holidays * and floating holidays. The list can be either a range of cells that * contains the dates or an array constant of the serial numbers that represent the dates.
* DO TEST && to test, uncomment this line
FUNCTION NetWorkDays(start_date as Date, end_date as Date, Holidays as array)
counter = 0 FOR count = 1 TO ALEN(Holidays) holiday = Holidays[count] IF(BETWEEN(holiday, start_date, end_date) and IsWorkDay(holiday)) counter = counter -1 ENDIF NEXT
DO WHILE start_date <=end_date IF IsWorkDay(start_date) counter = counter +1 ENDIF start_date=start_date +1 ENDDO
RETURN counter
FUNCTION IsWorkDay(thedate as Date) * ASSuMEs FDOW is 1, for VFP 6-7-8 compat. Add ,1 3rd parameter for VFP9, assumes US-based Sat/Sun weekends, RETURN BETWEEN(DOW(thedate),2,6)
PROCEDURE test DIMENSION Holidays[10] Holidays[1] = {^2018-01-01} Holidays[2] = {^2018-01-15} Holidays[3] = {^2018-02-19} Holidays[4] = {^2018-05-28} Holidays[5] = {^2018-07-04} Holidays[6] = {^2018-09-03} Holidays[7] = {^2018-10-08} Holidays[8] = {^2018-11-12} Holidays[9] = {^2018-11-22} Holidays[10] = {^2018-12-25}
ACTIVATE SCREEN CLEAR ? "July: " + TRANSFORM(NetWorkDays({^2018-07-01}, {^2018-7-31}, @Holidays)) ? "1st half: " + TRANSFORM(NetWorkDays({^2018-01-01}, {^2018-7-31}, @Holidays)) ? "Full Year: " + TRANSFORM(NetWorkDays({^2018-01-01}, {^2018-12-31}, @Holidays))
ENDPROC
On Fri, Sep 7, 2018 at 11:33 AM Ted Roche tedroche@gmail.com wrote:
On Wed, Sep 5, 2018 at 5:45 PM Richard Kaye rkaye@invaluable.com wrote:
1 - set the saved property to .t. before you get rid of the Excel object.
m.loExcel.ActiveWorkbook.Saved=.t.
2 - I bet Ted wants a crack at that one. 😊
--
rk
Oh, yeah, date math was my thing. From
https://support.office.com/en-us/article/NETWORKDAYS-function-48E717BF-A7A3-...
Is the spec. For VFP, we'll use an array of holidays range than Excel ranges...
BONUS: Validated against http://www.workingdays.us/
- Test Work days
- duplicate Excel function NETWORKDAYS
- NETWORKDAYS(start_date, end_date, [holidays])
- The NETWORKDAYS function syntax has the following arguments:
- Start_date Required. A date that represents the start date.
- End_date Required. A date that represents the end date.
- Holidays Optional. An optional range of one or more dates to
- exclude from the working calendar, such as state and federal holidays
- and floating holidays. The list can be either a range of cells that
- contains the dates or an array constant of the serial numbers that
represent the dates.
- DO TEST && to test, uncomment this line
FUNCTION NetWorkDays(start_date as Date, end_date as Date, Holidays as array)
counter = 0 FOR count = 1 TO ALEN(Holidays) holiday = Holidays[count] IF(BETWEEN(holiday, start_date, end_date) and IsWorkDay(holiday)) counter = counter -1 ENDIF NEXT
DO WHILE start_date <=end_date IF IsWorkDay(start_date) counter = counter +1 ENDIF start_date=start_date +1 ENDDO
RETURN counter
FUNCTION IsWorkDay(thedate as Date)
- ASSuMEs FDOW is 1, for VFP 6-7-8 compat. Add ,1 3rd parameter for VFP9,
assumes US-based Sat/Sun weekends, RETURN BETWEEN(DOW(thedate),2,6)
PROCEDURE test DIMENSION Holidays[10] Holidays[1] = {^2018-01-01} Holidays[2] = {^2018-01-15} Holidays[3] = {^2018-02-19} Holidays[4] = {^2018-05-28} Holidays[5] = {^2018-07-04} Holidays[6] = {^2018-09-03} Holidays[7] = {^2018-10-08} Holidays[8] = {^2018-11-12} Holidays[9] = {^2018-11-22} Holidays[10] = {^2018-12-25}
ACTIVATE SCREEN CLEAR ? "July: " + TRANSFORM(NetWorkDays({^2018-07-01}, {^2018-7-31}, @Holidays)) ? "1st half: " + TRANSFORM(NetWorkDays({^2018-01-01}, {^2018-7-31}, @Holidays)) ? "Full Year: " + TRANSFORM(NetWorkDays({^2018-01-01}, {^2018-12-31}, @Holidays))
ENDPROC