BAQ Calculated field using only Weekdays

In Progress ABL the WEEKDAY(date) function returns the day of the week as 1 = Sunday through 7 = Saturday.

You can create a loop to "walk" backwards or forwards a day at a time for n days and test the WEEKDAY(date) to see if you should increment or decrement n.

Below is what I use for this purpose ... in my case determining the number of working days between two given dates, but same logic applies. Note the CAN-DO("1,7", STRING(WEEKDAY(vdDate))) in the middle of the DO loop below.

I also check the production calendar in Epicor9 to see if that is a holiday or other non-working date. I needed that in a few other programs so I made it a function and put it into an include file to encourage reused.

{pny/standard.i} (not shown) declares, among other things:

DEF NEW GLOBAL SHARED VAR TOP-DEBUG AS LOGICAL.

This is tested to enable/disable debugging.

Cheers!

Christopher Heins
Sr. Progress Programmer/Analyst
[cid:image001.png@01CC8D78.82665C00]
Desk# 973-560-5370
Cell# 908-256-3662
Skype: cheins.nj
cheins@...<mailto:cheins@...>


/* pny/GetWorkDays.p - Given Start and End dates, compute number of workdays between them.

if Start < End then return positie.
if End < Start then return negative.

Sample usage:

def var viWorkDays as int no-undo.

run pny/GetWorkDays.p (CUR-COMP,
INPUT ttorderhed.OrderDate, /* Base Date */
INPUT ttorderhed.NeedByDate, /* 2nd Date */
INPUT "", /* options - Debug */
OUTPUT viWorkDays). /* Number of work days between the two days, exclusive */

run pny/debugmsg.p ("Workdays to build order: " + string(viWorkDays)).

*/

/* parameters */
def input param pCo as char no-undo.
def input param pStart as date no-undo.
def input param pEnd as date no-undo.
def input param pOptions as char no-undo.

def output param pWorkDays as int no-undo.

/* local variables */
def var n as int no-undo.
def var dir as int no-undo.
def var vlHoliday as logical no-undo.
def var vdDate as date no-undo. /* date we will manipulate */
def var viWallDays as int no-undo.
def var loops as int no-undo.


/* method libraries */
{pny/standard.i}

/* functions */
{pny/calendar.i} /* has Logical = fIsHoliday(Date) */


/* mainline */
top-debug = (pOptions matches "*debug*").
if top-debug then run pny/debugmsg.p ("BOJ").

if pStart = ? or pEnd = ? then return error "*** Invalid dates - cannot be null".
if pStart = pEnd then return. /* 0 days regardless */

assign
viWallDays = pEnd - pStart
vdDate = pStart
n = 0
dir = (if viWallDays < 0 then -1 else +1)
no-error.

do while true:
loops = loops + 1.
if (vdDate >= pEnd and dir > 0) or (vdDate <= pEnd and dir < 0) or loops > 100 then leave.

vlHoliday = fIsHoliday(pCo, vdDate).
if can-do("1,7", string(weekday(vdDate))) or vlHoliday then.
else n = n + 1. /* working day */

vdDate = vdDate + dir.
end.

assign pWorkDays = dir * n no-error.

if top-debug then run pny/debugmsg.p ("Start: " + nnstring(string(pStart))
+ " End: " + nnstring(string(pEnd))
+ " -> Workdays: " + nnString(string(pWorkDays))).

if top-debug then run pny/debugmsg.p ("EOJ").

return "".


/* pny/calendar.i - functions shared by calendar routines */

function fIsHoliday returns logical (pCo as char, pDate as date):
def var viWeekday as int no-undo.
if pDate = ? then return false.

viWeekday = weekday(pDate). /* 1 - 7 Sun ... Sat */
find first prodcal where prodcal.company = pCo no-lock no-error.

if prodcal.workweek[viWeekday] = false then return true.
else do: /* check to see if that particular day is non-working */
find prodCalDay
WHERE prodcalday.company = prodcal.company
and prodcalday.calendarID = prodcal.calendarID
and prodcalday.modifiedDay = pDate
no-lock no-error.
assign no-error.
if top-debug and available prodcalday
then run pny/debugmsg.p ("Holiday for date: " + string(pDate)
+ " Working: " + string(prodcalday.working) ).
if avail prodcalday and prodcalday.workingDay = false
then return true.
end.
return false.

end function.


________________________________
NOT INTENDED AS A SUBSTITUTE FOR A WRITING
NOTHING IN THIS E-MAIL, IN ANY E-MAIL THREAD OF WHICH IT MAY BE A PART, OR IN ANY ATTACHMENTS THERETO, SHALL CONSTITUTE A BINDING CONTRACT, OR ANY CONTRACTUAL OBLIGATION BY PNY, OR ANY INTENT TO ENTER INTO ANY BINDING OBLIGATIONS, NOTWITHSTANDING ANY ENACTMENT OF THE UNIFORM ELECTRONIC TRANSACTIONS ACT, THE FEDERAL E-SIGN ACT, OR ANY OTHER STATE OR FEDERAL LAW OF SIMILAR SUBSTANCE OR EFFECT. THIS EMAIL MESSAGE, ITS CONTENTS AND ATTACHMENTS ARE NOT INTENDED TO REPRESENT AN OFFER OR ACCEPTANCE OF AN OFFER TO ENTER INTO A CONTRACT. NOTHING IN THIS E-MAIL, IN ANY E-MAIL THREAD OF WHICH IT MAY BE A PART, OR IN ANY ATTACHMENTS THERETO SHALL ALTER THIS DISCLAIMER.

This e-mail message from PNY Technologies, Inc. is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.


[Non-text portions of this message have been removed]
In a BAQ, I created a calculated field trying to get a new date for the OrderRel.ReqDate less the ShipTo.EarlyBuffer using only the weekdays.

Can anyone help with the correct syntax to exclude weekends?

We are on Vantage 8.03.409C.