Try again...
Oh, yeah, date math was my thing. From
https://support.office.com/en-us/article/NETWORKDAYS-function-48E717BF-A7A3-495F-969E-5005E3EB18E7
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_dateRequired. A date that represents the start date.
* End_dateRequired. A date that represents the end date.
* HolidaysOptional. 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 wrote:
> On Wed, Sep 5, 2018 at 5:45 PM Richard Kaye 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-495F-969E-5005E3EB18E7
>
> 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_dateRequired. A date that represents the start date.
> * End_dateRequired. A date that represents the end date.
> * HolidaysOptional. 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
>
>
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list:
