# FW: BAQ - Count work days

Hi Everyone,

In case anyone is going to use this code you need to know that I forgot
to rem out one line in the New Year calculation when I added the part to
check if it fell on a weekend. See below between the dashed lines:

Oops!

Linda

' New Years

If Weekday(DateSerial(lngCount, 1, 1)) = 7 Then ' Saturday -
move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 1, 3)

Else

If Weekday(DateSerial(lngCount, 1, 1)) = 1 Then ' Sunday -
move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 1, 2)

Else

dteHoliday(lngACount) = DateSerial(lngCount, 1, 1)

End If

End If
'-----------------------------------------------------------------------
----------------------

THE NEXT LINE SHOULD HAVE BEEN REM'D OUT OR DELETED
dteHoliday(lngACount) = DateSerial(lngCount, 1, 1)

'-----------------------------------------------------------------------
----------------------

' MsgBox ("New Years day = " & dteHoliday(lngACount))

lngACount = lngACount + 1

' Family Day - 3rd Monday in February - Canada (AB, ON, SK)

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Linda Lowney
Sent: Tuesday, June 16, 2009 4:37 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ - Count work days

Hi Everyone,

I finally finished my working days project on the memo screen. I
borrowed and modified some code that I found on the internet to
calculate the holidays. We are in Canada, but I kept the USA holidays
in the formula, though I did not check the returned value of any of the
US holidays. Just change the value of strCountry from "Canada" to
"USA". If anyone can see any "programming errors" please let me know.
Here is the end product in case anyone else can make use of it.

Regards,

Linda

'//**************************************************

'// Custom VB.NET code for MemoForm

'// Created: 6/16/09

'//**************************************************

Imports System

Imports System.Data

Imports System.Diagnostics

Imports System.Windows.Forms

Imports System.ComponentModel

Imports Microsoft.VisualBasic

Imports Epicor.Mfg.UI

Imports Epicor.Mfg.UI.FrameWork

Imports Epicor.Mfg.UI.ExtendedProps

Imports Epicor.Mfg.UI.FormFunctions

Imports Epicor.Mfg.UI.Customization

Imports Epicor.Mfg.UI.Searches

Imports Epicor.Mfg.BO

Module Script

'// ** Wizard Insert Location - Do Not Remove 'Begin/End

'// Begin Wizard Added Module Level Variables **

'// End Wizard Added Module Level Variables **

Sub InitializeCustomCode()

'// ** Wizard Insert Location - Do not
delete 'Begin/End Wizard Added Variable Intialization' lines **

Intialization

Intialization

'// Begin Custom Method Calls

'// End Custom Method Calls

End Sub

Sub DestroyCustomCode()

'// ** Wizard Insert Location - Do not
delete 'Begin/End Wizard Added Object Disposal' lines **

'// Begin Wizard Added Object Disposal

'// End Wizard Added Object Disposal

'// Begin Custom Code Disposal

'// End Custom Code Disposal

End Sub

Private Sub Memo_AfterFieldChange(ByVal sender As object, ByVal args As
DataColumnChangeEventArgs) Handles Memo_Column.ColumnChanged

'// ** Argument Properties and Uses **

'// args.Row("[FieldName]")

'// args.Column, args.ProposedValue, args.Row

'

dim edvMemo As EpiDataView = CType(oTrans.EpiDataViews("Memo"),
EpiDataView)

dim epdStartDate as DateTime = dateserial(1980,1,1)

dim epdEndDate as DateTime = dateserial(1980,1,1)

dim lngWorkingDays as long = 0

dim strCountry as string = "Canada"

Select Case args.Column.ColumnName

Case "Date01"

if isdate(edvMemo.dataView(edvMemo.Row)("Date01")) and _

isdate(edvMemo.dataView(edvMemo.Row)("Date02")) then

epdStartDate = edvMemo.dataView(edvMemo.Row)("Date01")

epdEndDate = edvMemo.dataView(edvMemo.Row)("Date02")

if epdEndDate > epdStartDate then

epdEndDate, strCountry)

edvMemo.dataview(edvMemo.row)("Number01") =
cdbl(lngWorkingDays)

'msgbox("Working days = " & lngWorkingDays)

else

edvMemo.dataview(edvMemo.row)("Number01") = 0.00

end if

end if

Case "Date02"

if isdate(edvMemo.dataView(edvMemo.Row)("Date01")) and _

isdate(edvMemo.dataView(edvMemo.Row)("Date02")) then

epdStartDate = edvMemo.dataView(edvMemo.Row)("Date01")

epdEndDate = edvMemo.dataView(edvMemo.Row)("Date02")

if epdEndDate > epdStartDate then

epdEndDate, strCountry)

edvMemo.dataview(edvMemo.row)("Number01") =
cdbl(lngWorkingDays)

'msgbox("Working days = " & lngWorkingDays)

else

edvMemo.dataview(edvMemo.row)("Number01") = 0.00

end if

end if

Case Else

End Select

End Sub

Public Function BusinessDays(ByVal dteStartDate As Date, ByVal
dteEndDate As Date, ByVal strCountry As String) As Long

' Calculate Business Days - No Holiday Table Needed

Dim lngYear As Long

Dim lngEYear As Long

Dim dteStart As Date, dteEnd As Date

Dim dteCurr As Date

Dim lngDay As Long

Dim lngDiff As Long

Dim lngACount As Long

Dim dteLoop As long

Dim blnHol As Boolean

Dim dteHoliday() As Date

Dim lngCount As Long

Dim lngTotal As Long

dim lngFamily as long

Dim lngThanks As Long

Dim dteEaster As Date

dteStart = dteStartDate

dteEnd = dteEndDate

lngYear = DatePart("yyyy", dteStart)

lngEYear = DatePart("yyyy", dteEnd)

If lngYear <> lngEYear Then

lngDiff = (((lngEYear - lngYear) + 1) * 10) - 1

ReDim dteHoliday(lngDiff)

Else

ReDim dteHoliday(30)

End If

lngACount = -1

For lngCount = lngYear To lngEYear

lngACount = lngACount + 1

' New Years

If Weekday(DateSerial(lngCount, 1, 1)) = 7 Then ' Saturday -
move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 1, 3)

Else

If Weekday(DateSerial(lngCount, 1, 1)) = 1 Then ' Sunday -
move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 1, 2)

Else

dteHoliday(lngACount) = DateSerial(lngCount, 1, 1)

End If

End If

dteHoliday(lngACount) = DateSerial(lngCount, 1, 1)

' MsgBox ("New Years day = " & dteHoliday(lngACount))

lngACount = lngACount + 1

' Family Day - 3rd Monday in February - Canada (AB, ON, SK)

lngDay = 1

lngfamily = 0

Do

If Weekday(DateSerial(lngCount, 2, lngDay)) = 2 Then

lngfamily = lngfamily + 1

End If

lngDay = lngDay + 1

Loop Until lngfamily = 3

dteHoliday(lngACount) = DateSerial(lngCount, 2, lngDay - 1)

'MsgBox ("Family Day = " & dteHoliday(lngACount))

lngACount = lngACount + 1

End If

' Easter

lngDay = (((255 - 11 * (lngCount Mod 19)) - 21) Mod 30) + 21

dteEaster = DateSerial(lngCount, 3, 1)

(lngDay > 48) + 6 - ((lngCount + lngCount \ 4 + _

lngDay + (lngDay > 48) + 1)) Mod 7)

dteHoliday(lngACount) = DateSerial(dteEaster.Year,
dteEaster.Month, dteEaster.Day - 2)

'MsgBox ("Easter = " & dteHoliday(lngACount))

lngACount = lngACount + 1

' Victoria Day - Monday before May 25th - Canada

lngDay = 24

Do

If Weekday(DateSerial(lngCount, 5, lngDay)) = 2 Then

dteHoliday(lngACount) = DateSerial(lngCount, 5,
lngDay)

Else

lngDay = lngDay - 1

End If

Loop Until dteHoliday(lngACount) >= DateSerial(lngCount, 5,
1)

'MsgBox ("Victoria Day = " & dteHoliday(lngACount))

lngACount = lngACount + 1

End If

' Memorial Day - Last Monday of May

If strCountry = "USA" Then

lngDay = 31

Do

If Weekday(DateSerial(lngCount, 5, lngDay)) = 2 Then

dteHoliday(lngACount) = DateSerial(lngCount, 5,
lngDay)

Else

lngDay = lngDay - 1

End If

Loop Until dteHoliday(lngACount) >= DateSerial(lngCount, 5,
1)

lngACount = lngACount + 1

End If

' July 1st

If Weekday(DateSerial(lngCount, 7, 1)) = 7 Then ' Saturday -
move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 7, 3)

Else

If Weekday(DateSerial(lngCount, 7, 1)) = 1 Then ' Sunday
- move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 7, 2)

Else

dteHoliday(lngACount) = DateSerial(lngCount, 7, 1)

End If

End If

'MsgBox ("Canada Day = " & dteHoliday(lngACount))

lngACount = lngACount + 1

End If

' July 4th

If strCountry = "USA" Then

dteHoliday(lngACount) = DateSerial(lngCount, 7, 4)

lngACount = lngACount + 1

End If

' Civic Holiday - 1st Monday in August - Canada

lngDay = 1

lngfamily = 0

Do

If Weekday(DateSerial(lngCount, 8, lngDay)) = 2 Then

lngfamily = lngfamily + 1

End If

lngDay = lngDay + 1

Loop Until lngfamily = 1

dteHoliday(lngACount) = DateSerial(lngCount, 8, lngDay - 1)

'MsgBox ("Civic holiday = " & dteHoliday(lngACount))

lngACount = lngACount + 1

End If

' Labor Day - First Monday of Septemeber

lngDay = 1

Do

If Weekday(DateSerial(lngCount, 9, lngDay)) = 2 Then

dteHoliday(lngACount) = DateSerial(lngCount, 9, lngDay)

Else

lngDay = lngDay + 1

End If

Loop Until dteHoliday(lngACount) >= DateSerial(lngCount, 9, 1)

'MsgBox ("Labor day = " & dteHoliday(lngACount))

' Thanksgiving - 2nd Monday of October

lngDay = 1

lngThanks = 0

Do

If Weekday(DateSerial(lngCount, 10, lngDay)) = 1 Then

lngThanks = lngThanks + 1

End If

lngDay = lngDay + 1

Loop Until lngThanks = 2

dteHoliday(lngACount) = DateSerial(lngCount, 10, lngDay)

'MsgBox ("Thanksgiving day = " & dteHoliday(lngACount))

lngACount = lngACount + 1

End If

' Thanksgiving - 4th Thursday of November

If strCountry = "USA" Then

lngDay = 1

lngThanks = 0

Do

If Weekday(DateSerial(lngCount, 11, lngDay)) = 5 Then

lngThanks = lngThanks + 1

End If

lngDay = lngDay + 1

Loop Until lngThanks = 4

dteHoliday(lngACount) = DateSerial(lngCount, 11, lngDay)

lngACount = lngACount + 1

End If

' Christmas

If Weekday(DateSerial(lngCount, 12, 25)) = 7 Then ' Saturday -
move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 12, 27)

Else

If Weekday(DateSerial(lngCount, 12, 25)) = 1 Then ' Sunday -
move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 12, 26)

Else

dteHoliday(lngACount) = DateSerial(lngCount, 12, 25)

End If

End If

'MsgBox ("Christmas = " & dteHoliday(lngACount))

If strCountry = "Canada" Then ' In US Christmas will be last
holiday of year - don't need to add counter

lngACount = lngACount + 1

End If

' Boxing Day

If Weekday(DateSerial(lngCount, 12, 26)) = 7 Then ' Saturday
- move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 12, 28)

Else

If Weekday(DateSerial(lngCount, 12, 26)) = 1 Then '
Sunday - move to Monday

dteHoliday(lngACount) = DateSerial(lngCount, 12, 27)

Else

dteHoliday(lngACount) = DateSerial(lngCount, 12, 26)

End If

End If

'MsgBox ("Boxing Day = " & dteHoliday(lngACount))

End If

Next

' Weekends

For lngCount = 1 To DateDiff("d", dteStart, dteEnd)

If (Weekday(dteCurr) <> 1) And (Weekday(dteCurr) <> 7) Then

blnHol = False

For dteLoop = 0 To UBound(dteHoliday)

'MsgBox dteHoliday(dteLoop) & " " & dteLoop

If (dteHoliday(dteLoop) = dteCurr) Then

blnHol = True

End If

Next dteLoop

If blnHol = False Then

lngTotal = lngTotal + 1

'MsgBox dteCurr

End If

End If

Next lngCount

End Function

End Module

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Robert Brown
Sent: Monday, June 08, 2009 5:10 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] BAQ - Count work days

Ari,

Sure. The 'canned' calender table schema is a little overly complex for
most date math purposes (for my tastes) - We set up a much cleaner UD
table (much like Vic just suggested) - but they are certainly usable.

The problem lies with the very limited Progress 4GL support within BAQ
calculated fields. (It's doable with SQL.)

You could create the syntax to do the loop to test from start.date + 1
thru end.date - but I think you could only test for weekend dates. I
don't see support for traversing thru every record of the unjoined
calendar table to test for a holiday.

Any ideas?!?!?!

Rob Brown

________________________________
From: Ari Footlik <ari@... <mailto:ari%40zweig-cnc.com>
<mailto:ari%40zweig-cnc.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Sent: Monday, June 8, 2009 3:02:03 PM
Subject: RE: [Vantage] BAQ - Count work days

Couldn't you create a company calendar, not link it to any resources,
and set the "work days" on it to reflect your organization' s schedule?

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On
Behalf
Of Robert Brown
Sent: Monday, June 08, 2009 1:22 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] BAQ - Count work days

The weekend stuff is pretty easy. Take Rob's formula and enclose it
with: INTEGER(5/7* (end.date - start.date) - "5" assuming a 5 day
standard week.

4GL also has a WEEKDAY(yourdate. field) function that returns (integer)
1
for Sunday, 2 Monday, etc.,.
(Amazingly the braindead BAQ wizard actually understands that
function... It doesn't understand most of the potentially useful 4GL
functions.)

The problem is with the holidays.

2. Never tried it but I suspect if you left this 'holiday' table
unjoined to all others (which you would have to - like you would in
SQL), the BAQ would hang (or run awful slow).
2. Even with a table source you can't write a looping procedure with
defined variables inside a BAQ calc field that would allow you to start
with one date and go forward (or backward) testing for weekends and
holidays (through the unjoined table) and count valid workdays until you

Your best bet is to do it with multiple BAQs (one for your start/end
date records, one for the holiday table) and make the dashboard, excel
report or crystal report do the work.

If you are doing this in an app, you can bring the BAQ results into
arrays & then use VB custom code to calc the days (and redisplay the
result in a 3rd array in an ultragrid).

A good example of VB code for calculating workdays:

http://social. msdn.microsoft. com/Forums/ en-US/vbgeneral/
6984-4b24-a44e- 3bcc09ab1e1e/
<http://social. msdn.microsoft. com/Forums/ en-US/vbgeneral/
-6984-4b24-a44e- 3bcc09ab1e1e/ >

Best of luck Linda. Would love to see it if you come up with something.

Rob Brown

____________ _________ _________ __
From: Rob Bucek <rbucek@dsmfg. com <mailto:rbucek% 40dsmfg.com> >
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Sent: Monday, June 8, 2009 1:12:13 PM
Subject: RE: [Vantage] BAQ - Count work days

Without doing a lot more investigation I couldn't tell you that. I do
know that BAQ calculated fields can be very finicky, and that (at least
up to 407) they cannot reference other calculated fields. You can do a
fair bit of date manipulation and calculation with progress, but I don't
have an easy solution for that. Perhaps a more knowledgeable progress
person might. Sorry..

Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg. com/>

(Click the logo to view our site)

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@ yahoogroups .com] On
Behalf
Of Linda Lowney
Sent: Monday, June 08, 2009 12:06 PM
To: vantage@yahoogroups .com
Subject: RE: [Vantage] BAQ - Count work days

Hi Rob,

Thank you, but I need working days - no weekends - no holidays. I can
do this in Crystal and Excel, but I don't know much about creating
calculated fields in BAQ Progress.

Regards,

Linda

From: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
[mailto:vantage@ yahoogroups .com <mailto:vantage% 40yahoogroups. com> ]
On
Behalf
Of Rob Bucek
Sent: Monday, June 08, 2009 12:50 PM
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Subject: RE: [Vantage] BAQ - Count work days

An example of syntax

Format

>>99 (this will give you a two digit Integer)

Data Type

Integer

It is possible to get what you are after.

Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg. com/ <http://www.dsmfg. com/> >

(Click the logo to view our site)

____________ _________ _________ __

From: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
<mailto:vantage% 40yahoogroups. com>
[mailto:vantage@ yahoogroups .com <mailto:vantage% 40yahoogroups. com>
<mailto:vantage% 40yahoogroups. com> ] On
Behalf
Of lindalowney
Sent: Monday, June 08, 2009 10:59 AM
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
<mailto:vantage% 40yahoogroups. com>
Subject: [Vantage] BAQ - Count work days

Hi Group,

I need to create a field in a BAQ showing the number of working days
between two dates. Is it possible to create a calculation field to
provide this information?

Regards,
Linda

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]