I have been struggling with this for a few days. Racked my brains.
Coming up with dead ends. Searched through the web & groups. It is
both the 'puzzle solving' and VBA looping logic I am having problems
with. Can anyone give me some pointers?

I need to calculate the time it takes to complete an application,
accounting for:

- workday 6 am - 6 pm (clock stops during non-work hours)
- holidays listed in a separate table with begin & end date/times
(clock stops on holidays)
- 'events' which are in separate table related to each application and
have start and end date/times (clock stops until 'event' is complete)

I need to determine if the time to complete the application was within
a service agreement 'max time to complete'.

I have three tables:

1. Application - has Application Start and Completed date/time fields
and each application has unique Application_ID

2. Events - each event has unique Events_ID and foreign key relating
to each Application_ID

3. Holidays - days that are regular holidays and specific company
holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours

I need to determine the following:

1. Total time to complete excluding holidays and working hours, and
event durations.

2. Expected date/time of completion based on current date/time
excluding holidays and working hours, and event durations.

************************************************
Example data:

Application Start: July 1, 2008 13:00 (clock starts)

Event 1 Start: July 1, 2008 14:00 (clock stops)
Event 1 End: July 2, 2008 11:00 (clock starts)

Event 2 Start: July 2, 2008 16:00 (clock stops)
Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
working hours so clock would start at next working day eg July 4 06:00
but July 4 was a holiday so clock starts July 5 06:00 instead)

Application Completed: July 6, 2008 13:00 (clock stops)
*******************************************************

My conjectured VBA so far:

' Declare variables
' Create a DAO recordset for Events grouped by Application to loop
through

' Does Application have any Events?
If recordset has no events for application then
' Does Application have an Application_Completion date?
If no Application_Completion date then
' Calculate duration using current date (ie still open)
SLA_Duration = DateDiff("d" Application_Start, Now ())
Else
' Calculate duration using completion date
SLA_Duration = DateDiff("d" Application_Start,
Application_Completion )
End if
Else ' Application has Events
' Loop through events calculating
i = i + 1
1 -- Application_Start to Event 0 Start ---> DateDiff("d"
Application_Start, Event 0 start)
2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
Event_End)
3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
+1 End, Application_Completion)
SLA_Duration = Sum(1 + 2 + 3)
Next i
End loop
End If

Where do I put in the logic that tests whether any of the dates is
outside of workday hours or a holiday? Then how do I have the logic
'jump ahead' to the next work day start to use that date/time instead
to start the clock and perform calculations?

Any pointers at all greatly appreciated!

Genoki

Are you working with existing data or desiging a database? If its the later,
just create a database where each person working on the project logs in and
logs out for each period that they work on it as is....

Start End
Person
7/21/2008 8:00 AM 7/21/2008 10:00 PM Frank
7/22/2008 7:00 AM 7/22/2008 8:00 AM Susan

And then add up the duration of each record.

"(E-Mail Removed)" wrote:

> I have been struggling with this for a few days. Racked my brains.
> Coming up with dead ends. Searched through the web & groups. It is
> both the 'puzzle solving' and VBA looping logic I am having problems
> with. Can anyone give me some pointers?
>
> I need to calculate the time it takes to complete an application,
> accounting for:
>
> - workday 6 am - 6 pm (clock stops during non-work hours)
> - holidays listed in a separate table with begin & end date/times
> (clock stops on holidays)
> - 'events' which are in separate table related to each application and
> have start and end date/times (clock stops until 'event' is complete)
>
> I need to determine if the time to complete the application was within
> a service agreement 'max time to complete'.
>
> I have three tables:
>
> 1. Application - has Application Start and Completed date/time fields
> and each application has unique Application_ID
>
> 2. Events - each event has unique Events_ID and foreign key relating
> to each Application_ID
>
> 3. Holidays - days that are regular holidays and specific company
> holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
>
> I need to determine the following:
>
> 1. Total time to complete excluding holidays and working hours, and
> event durations.
>
> 2. Expected date/time of completion based on current date/time
> excluding holidays and working hours, and event durations.
>
> ************************************************
> Example data:
>
> Application Start: July 1, 2008 13:00 (clock starts)
>
> Event 1 Start: July 1, 2008 14:00 (clock stops)
> Event 1 End: July 2, 2008 11:00 (clock starts)
>
> Event 2 Start: July 2, 2008 16:00 (clock stops)
> Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
> working hours so clock would start at next working day eg July 4 06:00
> but July 4 was a holiday so clock starts July 5 06:00 instead)
>
> Application Completed: July 6, 2008 13:00 (clock stops)
> *******************************************************
>
> My conjectured VBA so far:
>
> ' Declare variables
> ' Create a DAO recordset for Events grouped by Application to loop
> through
>
> ' Does Application have any Events?
> If recordset has no events for application then
> ' Does Application have an Application_Completion date?
> If no Application_Completion date then
> ' Calculate duration using current date (ie still open)
> SLA_Duration = DateDiff("d" Application_Start, Now ())
> Else
> ' Calculate duration using completion date
> SLA_Duration = DateDiff("d" Application_Start,
> Application_Completion )
> End if
> Else ' Application has Events
> ' Loop through events calculating
> i = i + 1
> 1 -- Application_Start to Event 0 Start ---> DateDiff("d"
> Application_Start, Event 0 start)
> 2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
> Event_End)
> 3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
> +1 End, Application_Completion)
> SLA_Duration = Sum(1 + 2 + 3)
> Next i
> End loop
> End If
>
> Where do I put in the logic that tests whether any of the dates is
> outside of workday hours or a holiday? Then how do I have the logic
> 'jump ahead' to the next work day start to use that date/time instead
> to start the clock and perform calculations?
>
> Any pointers at all greatly appreciated!
>
> Genoki
>

On Jul 21, 8:13 pm, gen...@yahoo.com wrote:
> I have been struggling with this for a few days. Racked my brains.
> Coming up with dead ends. Searched through the web & groups. It is
> both the 'puzzle solving' and VBA looping logic I am having problems
> with. Can anyone give me some pointers?
>
> I need to calculate the time it takes to complete an application,
> accounting for:
>
> - workday 6 am - 6 pm (clock stops during non-work hours)
> - holidays listed in a separate table with begin & end date/times
> (clock stops on holidays)
> - 'events' which are in separate table related to each application and
> have start and end date/times (clock stops until 'event' is complete)
>
> I need to determine if the time to complete the application was within
> a service agreement 'max time to complete'.
>
> I have three tables:
>
> 1. Application - has Application Start and Completed date/time fields
> and each application has unique Application_ID
>
> 2. Events - each event has unique Events_ID and foreign key relating
> to each Application_ID
>
> 3. Holidays - days that are regular holidays and specific company
> holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
>
> I need to determine the following:
>
> 1. Total time to complete excluding holidays and working hours, and
> event durations.
>
> 2. Expected date/time of completion based on current date/time
> excluding holidays and working hours, and event durations.
>
> ************************************************
> Example data:
>
> Application Start: July 1, 2008 13:00 (clock starts)
>
> Event 1 Start: July 1, 2008 14:00 (clock stops)
> Event 1 End: July 2, 2008 11:00 (clock starts)
>
> Event 2 Start: July 2, 2008 16:00 (clock stops)
> Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
> working hours so clock would start at next working day eg July 4 06:00
> but July 4 was a holiday so clock starts July 5 06:00 instead)
>
> Application Completed: July 6, 2008 13:00 (clock stops)
> *******************************************************
>
> My conjectured VBA so far:
>
> ' Declare variables
> ' Create a DAO recordset for Events grouped by Application to loop
> through
>
> ' Does Application have any Events?
> If recordset has no events for application then
> ' Does Application have an Application_Completion date?
> If no Application_Completion date then
> ' Calculate duration using current date (ie still open)
> SLA_Duration = DateDiff("d" Application_Start, Now ())
> Else
> ' Calculate duration using completion date
> SLA_Duration = DateDiff("d" Application_Start,
> Application_Completion )
> End if
> Else ' Application has Events
> ' Loop through events calculating
> i = i + 1
> 1 -- Application_Start to Event 0 Start ---> DateDiff("d"
> Application_Start, Event 0 start)
> 2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
> Event_End)
> 3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
> +1 End, Application_Completion)
> SLA_Duration = Sum(1 + 2 + 3)
> Next i
> End loop
> End If
>
> Where do I put in the logic that tests whether any of the dates is
> outside of workday hours or a holiday? Then how do I have the logic
> 'jump ahead' to the next work day start to use that date/time instead
> to start the clock and perform calculations?
>
> Any pointers at all greatly appreciated!
>
> Genoki

not entirely sure where to start, so I'll just pick a place.
To do the calendar math, use the code from Arvin Meyer's website.

http://www.datastrat.com/Code/GetBusinessDay.txt

From there, you can get the time required to complete task X. He does
not assume a workday of 8AM to 8PM, but you could do that. Your full
days would not be 24 hours, but whatever hours overlapped with a
workday.

Thanks dch3,

Its an existing database though I can change it. However, I am doing
what you suggest in my 'Events' table ie they are logging start and
stop date/times (but might have none or many 'Events')..

However, what I am really having a hard time with is how to use those
durations but only count the time the is either

a) during regular business hours and
b) not on in designated holidays in the holiday table (which have
their own start and end times)

To use your example as an example, Frank's End time is 10 pm, but
since regular business hours end at 6 pm, the calculation of the due
date and duration, must end at 6 pm, continuing at 6 am next morning.
The clock stops out of regular business hours.

Thanks again!

Genoki


On Jul 21, 8:09 pm, dch3 <d...@discussions.microsoft.com> wrote:
> Are you working with existing data or desiging a database? If its the later,
> just create a database where each person working on the project logs in and
> logs out for each period that they work on it as is....
>
> Start End
> Person
> 7/21/2008 8:00 AM 7/21/2008 10:00 PM Frank
> 7/22/2008 7:00 AM 7/22/2008 8:00 AM Susan
>
> And then add up the duration of each record.
>
> "gen...@yahoo.com" wrote:
> > I have been struggling with this for a few days. Racked my brains.
> > Coming up with dead ends. Searched through the web & groups. It is
> > both the 'puzzle solving' and VBA looping logic I am having problems
> > with. Can anyone give me some pointers?
>
> > I need to calculate the time it takes to complete an application,
> > accounting for:
>
> > - workday 6 am - 6 pm (clock stops during non-work hours)
> > - holidays listed in a separate table with begin & end date/times
> > (clock stops on holidays)
> > - 'events' which are in separate table related to each application and
> > have start and end date/times (clock stops until 'event' is complete)
>
> > I need to determine if the time to complete the application was within
> > a service agreement 'max time to complete'.
>
> > I have three tables:
>
> > 1. Application - has Application Start and Completed date/time fields
> > and each application has unique Application_ID
>
> > 2. Events - each event has unique Events_ID and foreign key relating
> > to each Application_ID
>
> > 3. Holidays - days that are regular holidays and specific company
> > holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
>
> > I need to determine the following:
>
> > 1. Total time to complete excluding holidays and working hours, and
> > event durations.
>
> > 2. Expected date/time of completion based on current date/time
> > excluding holidays and working hours, and event durations.
>
> > ************************************************
> > Example data:
>
> > Application Start: July 1, 2008 13:00 (clock starts)
>
> > Event 1 Start: July 1, 2008 14:00 (clock stops)
> > Event 1 End: July 2, 2008 11:00 (clock starts)
>
> > Event 2 Start: July 2, 2008 16:00 (clock stops)
> > Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
> > working hours so clock would start at next working day eg July 4 06:00
> > but July 4 was a holiday so clock starts July 5 06:00 instead)
>
> > Application Completed: July 6, 2008 13:00 (clock stops)
> > *******************************************************
>
> > My conjectured VBA so far:
>
> > ' Declare variables
> > ' Create a DAO recordset for Events grouped by Application to loop
> > through
>
> > ' Does Application have any Events?
> > If recordset has no events for application then
> > ' Does Application have an Application_Completion date?
> > If no Application_Completion date then
> > ' Calculate duration using current date (ie still open)
> > SLA_Duration = DateDiff("d" Application_Start, Now ())
> > Else
> > ' Calculate duration using completion date
> > SLA_Duration = DateDiff("d" Application_Start,
> > Application_Completion )
> > End if
> > Else ' Application has Events
> > ' Loop through events calculating
> > i = i + 1
> > 1 -- Application_Start to Event 0 Start ---> DateDiff("d"
> > Application_Start, Event 0 start)
> > 2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
> > Event_End)
> > 3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
> > +1 End, Application_Completion)
> > SLA_Duration = Sum(1 + 2 + 3)
> > Next i
> > End loop
> > End If
>
> > Where do I put in the logic that tests whether any of the dates is
> > outside of workday hours or a holiday? Then how do I have the logic
> > 'jump ahead' to the next work day start to use that date/time instead
> > to start the clock and perform calculations?
>
> > Any pointers at all greatly appreciated!
>
> >Genoki

pietlinden

The Arvin Meyer example is tantalizing. I wish, i could grasp how to
use it practically. I feel it is half the solution to my challenge.
It tells me how to add up time exluding non business days.

But, I am not sure if it can help me account for my events ...is it
possible that I can re-use the entire code but direct it at my Events
recordset? But I am still struggling as to how to loop through a
recordset of Events, and look at each record in turn, evaluating it as
to whether the Start and/or End dates are/aren't in regular hours and
holidays.

After I finish this contract, I am giving up Access development. I
really feel as if I have gone as far as I can. I have built lots of
useful Access applications used by 100's of people in some major
organizations but when it comes to this more complex VBA looping and
logic I am truly out of my depth. I have watched 'real' database
developers and application programmers come up with really slick,
complex code and solutions and feel awed ...

Sigh .... help!

Genoki





On Jul 21, 8:21 pm, "pietlin...@hotmail.com" <pietlin...@hotmail.com>
wrote:
> On Jul 21, 8:13 pm, gen...@yahoo.com wrote:
>
>
>
> > I have been struggling with this for a few days. Racked my brains.
> > Coming up with dead ends. Searched through the web & groups. It is
> > both the 'puzzle solving' and VBA looping logic I am having problems
> > with. Can anyone give me some pointers?
>
> > I need to calculate the time it takes to complete an application,
> > accounting for:
>
> > - workday 6 am - 6 pm (clock stops during non-work hours)
> > - holidays listed in a separate table with begin & end date/times
> > (clock stops on holidays)
> > - 'events' which are in separate table related to each application and
> > have start and end date/times (clock stops until 'event' is complete)
>
> > I need to determine if the time to complete the application was within
> > a service agreement 'max time to complete'.
>
> > I have three tables:
>
> > 1. Application - has Application Start and Completed date/time fields
> > and each application has unique Application_ID
>
> > 2. Events - each event has unique Events_ID and foreign key relating
> > to each Application_ID
>
> > 3. Holidays - days that are regular holidays and specific company
> > holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
>
> > I need to determine the following:
>
> > 1. Total time to complete excluding holidays and working hours, and
> > event durations.
>
> > 2. Expected date/time of completion based on current date/time
> > excluding holidays and working hours, and event durations.
>
> > ************************************************
> > Example data:
>
> > Application Start: July 1, 2008 13:00 (clock starts)
>
> > Event 1 Start: July 1, 2008 14:00 (clock stops)
> > Event 1 End: July 2, 2008 11:00 (clock starts)
>
> > Event 2 Start: July 2, 2008 16:00 (clock stops)
> > Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
> > working hours so clock would start at next working day eg July 4 06:00
> > but July 4 was a holiday so clock starts July 5 06:00 instead)
>
> > Application Completed: July 6, 2008 13:00 (clock stops)
> > *******************************************************
>
> > My conjectured VBA so far:
>
> > ' Declare variables
> > ' Create a DAO recordset for Events grouped by Application to loop
> > through
>
> > ' Does Application have any Events?
> > If recordset has no events for application then
> > ' Does Application have an Application_Completion date?
> > If no Application_Completion date then
> > ' Calculate duration using current date (ie still open)
> > SLA_Duration = DateDiff("d" Application_Start, Now ())
> > Else
> > ' Calculate duration using completion date
> > SLA_Duration = DateDiff("d" Application_Start,
> > Application_Completion )
> > End if
> > Else ' Application has Events
> > ' Loop through events calculating
> > i = i + 1
> > 1 -- Application_Start to Event 0 Start ---> DateDiff("d"
> > Application_Start, Event 0 start)
> > 2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
> > Event_End)
> > 3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
> > +1 End, Application_Completion)
> > SLA_Duration = Sum(1 + 2 + 3)
> > Next i
> > End loop
> > End If
>
> > Where do I put in the logic that tests whether any of the dates is
> > outside of workday hours or a holiday? Then how do I have the logic
> > 'jump ahead' to the next work day start to use that date/time instead
> > to start the clock and perform calculations?
>
> > Any pointers at all greatly appreciated!
>
> >Genoki
>
> not entirely sure where to start, so I'll just pick a place.
> To do the calendar math, use the code from Arvin Meyer's website.
>
> http://www.datastrat.com/Code/GetBusinessDay.txt
>
> From there, you can get the time required to complete task X. He does
> not assume a workday of 8AM to 8PM, but you could do that. Your full
> days would not be 24 hours, but whatever hours overlapped with a
> workday.

Sorry, but the BSA (Business Systems Analyst) in me is coming out. If the
goal is to document the total amount of time devoted to developing the
application, whether or not the time was after hours, on weekends, or a
holiday is irrelevant. 40 hours is 40 hours. Why would you need the clock to
start over the during the next 'regular' day? What is the specific business
goal in doing that? If you're trying to break out hours on regular time,
after hours, weekends and holidays, then I would suggest a function that
takes the START and END times and a third parameter that indicates time to
calculate (Regular, Afterhours, Weekend, Holidays) and then does the math
using DateDiff().

In a report design mode, it'd look like

Start Time End Time Regular Hours After Hours Weekend
Holiday
dteStartTime dteEndTime getHours(ST,EN, "Regular")

You'd probably also want to include totals for each category and a line
total to confirm that the math's all correct.

Does that help to move things along?

"(E-Mail Removed)" wrote:

> Thanks dch3,
>
> Its an existing database though I can change it. However, I am doing
> what you suggest in my 'Events' table ie they are logging start and
> stop date/times (but might have none or many 'Events')..
>
> However, what I am really having a hard time with is how to use those
> durations but only count the time the is either
>
> a) during regular business hours and
> b) not on in designated holidays in the holiday table (which have
> their own start and end times)
>
> To use your example as an example, Frank's End time is 10 pm, but
> since regular business hours end at 6 pm, the calculation of the due
> date and duration, must end at 6 pm, continuing at 6 am next morning.
> The clock stops out of regular business hours.
>
> Thanks again!
>
> Genoki
>
>
> On Jul 21, 8:09 pm, dch3 <d...@discussions.microsoft.com> wrote:
> > Are you working with existing data or desiging a database? If its the later,
> > just create a database where each person working on the project logs in and
> > logs out for each period that they work on it as is....
> >
> > Start End
> > Person
> > 7/21/2008 8:00 AM 7/21/2008 10:00 PM Frank
> > 7/22/2008 7:00 AM 7/22/2008 8:00 AM Susan
> >
> > And then add up the duration of each record.
> >
> > "gen...@yahoo.com" wrote:
> > > I have been struggling with this for a few days. Racked my brains.
> > > Coming up with dead ends. Searched through the web & groups. It is
> > > both the 'puzzle solving' and VBA looping logic I am having problems
> > > with. Can anyone give me some pointers?
> >
> > > I need to calculate the time it takes to complete an application,
> > > accounting for:
> >
> > > - workday 6 am - 6 pm (clock stops during non-work hours)
> > > - holidays listed in a separate table with begin & end date/times
> > > (clock stops on holidays)
> > > - 'events' which are in separate table related to each application and
> > > have start and end date/times (clock stops until 'event' is complete)
> >
> > > I need to determine if the time to complete the application was within
> > > a service agreement 'max time to complete'.
> >
> > > I have three tables:
> >
> > > 1. Application - has Application Start and Completed date/time fields
> > > and each application has unique Application_ID
> >
> > > 2. Events - each event has unique Events_ID and foreign key relating
> > > to each Application_ID
> >
> > > 3. Holidays - days that are regular holidays and specific company
> > > holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
> >
> > > I need to determine the following:
> >
> > > 1. Total time to complete excluding holidays and working hours, and
> > > event durations.
> >
> > > 2. Expected date/time of completion based on current date/time
> > > excluding holidays and working hours, and event durations.
> >

Here are two functions. One that calculates the number of working days
between two dates. It excludes Saturdays, Sundays, and any date in the
holiday table. To add an event table, you could easily copy the code used to
count holidays. But, I would suggest that both holidays and events be in the
same table as they are logically the same in your case. If you need to
differentiate between them, add a field to the holiday table that indentifies
each.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays tabl
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The table
should
' : Contain one row for each holiday. As written, the table is
named
' : tblHolidays and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values
may produce
' : Incorrect Result
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


"(E-Mail Removed)" wrote:

> I have been struggling with this for a few days. Racked my brains.
> Coming up with dead ends. Searched through the web & groups. It is
> both the 'puzzle solving' and VBA looping logic I am having problems
> with. Can anyone give me some pointers?
>
> I need to calculate the time it takes to complete an application,
> accounting for:
>
> - workday 6 am - 6 pm (clock stops during non-work hours)
> - holidays listed in a separate table with begin & end date/times
> (clock stops on holidays)
> - 'events' which are in separate table related to each application and
> have start and end date/times (clock stops until 'event' is complete)
>
> I need to determine if the time to complete the application was within
> a service agreement 'max time to complete'.
>
> I have three tables:
>
> 1. Application - has Application Start and Completed date/time fields
> and each application has unique Application_ID
>
> 2. Events - each event has unique Events_ID and foreign key relating
> to each Application_ID
>
> 3. Holidays - days that are regular holidays and specific company
> holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
>
> I need to determine the following:
>
> 1. Total time to complete excluding holidays and working hours, and
> event durations.
>
> 2. Expected date/time of completion based on current date/time
> excluding holidays and working hours, and event durations.
>
> ************************************************
> Example data:
>
> Application Start: July 1, 2008 13:00 (clock starts)
>
> Event 1 Start: July 1, 2008 14:00 (clock stops)
> Event 1 End: July 2, 2008 11:00 (clock starts)
>
> Event 2 Start: July 2, 2008 16:00 (clock stops)
> Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
> working hours so clock would start at next working day eg July 4 06:00
> but July 4 was a holiday so clock starts July 5 06:00 instead)
>
> Application Completed: July 6, 2008 13:00 (clock stops)
> *******************************************************
>
> My conjectured VBA so far:
>
> ' Declare variables
> ' Create a DAO recordset for Events grouped by Application to loop
> through
>
> ' Does Application have any Events?
> If recordset has no events for application then
> ' Does Application have an Application_Completion date?
> If no Application_Completion date then
> ' Calculate duration using current date (ie still open)
> SLA_Duration = DateDiff("d" Application_Start, Now ())
> Else
> ' Calculate duration using completion date
> SLA_Duration = DateDiff("d" Application_Start,
> Application_Completion )
> End if
> Else ' Application has Events
> ' Loop through events calculating
> i = i + 1
> 1 -- Application_Start to Event 0 Start ---> DateDiff("d"
> Application_Start, Event 0 start)
> 2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
> Event_End)
> 3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
> +1 End, Application_Completion)
> SLA_Duration = Sum(1 + 2 + 3)
> Next i
> End loop
> End If
>
> Where do I put in the logic that tests whether any of the dates is
> outside of workday hours or a holiday? Then how do I have the logic
> 'jump ahead' to the next work day start to use that date/time instead
> to start the clock and perform calculations?
>
> Any pointers at all greatly appreciated!
>
> Genoki
>

Thanks Klatuu & dch3,

I think I have enough to solve my challenge.

Klatuu your suggestion to consider Events and Holidays as logically
the same, makes the solution simpler. However, I won't put them into
same table but will union them and then work with them.

dch3, your suggestion to break out reporting of hours into working
hours, weekends, holidays, events helped to clarify what I need to do.

I will post my solution when I work it out.

Cheers,

Genoki

On Jul 22, 7:18*am, Klatuu <Kla...@discussions.microsoft.com> wrote:
> Here are two functions. *One that calculates the number of working days
> between two dates. *It excludes Saturdays, Sundays, and any date in the
> holiday table. *To add an event table, you could easily copy the code used to
> count holidays. *But, I would suggest that both holidays and events be in the
> same table as they are logically the same in your case. *If you need to
> differentiate between them, add a field to the holiday table that indentifies
> each.
>
> '--------------------------------------------------------------------------*-------------
> ' Procedure : CalcWorkDays
> ' DateTime *: 5/8/2006 16:34
> ' Author * *: Dave Hargis
> ' Purpose * : Counts the number of days between two dates excluding Saturdays,
> ' * * * * * : Sundays, and any days in the Holidays table
> '--------------------------------------------------------------------------*-------------
> '
> Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
>
> * * On Error GoTo CalcWorkDays_Error
>
> 'Calculates the number of days between the dates
> 'Add one so all days are included
> * * CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
> * * * * (DateDiff("ww", dtmStart, dtmEnd, 7) + _
> * * * * DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
> 'Subtract the Holidays
> * * CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
> between #" _
> * * * * & dtmStart & "# And #" & dtmEnd & "#")
>
> CalcWorkDays_Exit:
>
> * * On Error Resume Next
> * * Exit Function
>
> CalcWorkDays_Error:
>
> * * MsgBox "Error " & Err.Number & " (" & Err.Description & _
> * * * * ") in procedure CalcWorkDays of Module modDateFunctions"
> * * GoTo CalcWorkDays_Exit
>
> End Function
>
> '--------------------------------------------------------------------------*-------------
> ' Procedure : AddWorkDays
> ' DateTime *: 5/8/2006 14:24
> ' Author * *: Dave Hargis
> ' Purpose * : Determines the date of a work day (mon-fri) a number of days
> after or
> ' * * * * * : before a given date.
> ' Arguments : OriginalDate *The date you want to add or subtract from to
> determine a
> ' * * * * * : * * * * * * * worday
> ' * * * * * : DaysToAdd * * Either a positive or negative number of working
> days
> ' * * * * * : Saturdays and Sundays are not counted
> ' * * * * * : Holidays can also be omitted using a holiday table. The table
> should
> ' * * * * * : Contain one row for each holiday. *As written, the table is
> named
> ' * * * * * : tblHolidays and the field used is a Date field named Holidate
> ' * * * * * : No time vaule should be in the Holidate field. *Time values
> may produce
> ' * * * * * : Incorrect Results
> '--------------------------------------------------------------------------*-------------
> '
> Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
> Dim lngAdd As Long
> Dim lngDayCount As Long
> Dim lngHolidayCount As Long
>
> * * On Error GoTo AddWorkDays_Error
>
> * * If DaysToAdd < 0 Then
> * * * * lngAdd = -1
> * * Else
> * * * * lngAdd = 1
> * * End If
>
> * * AddWorkDays = OriginalDate
>
> * * Do Until lngDayCount = DaysToAdd
> * * * * AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
> * * * * If Weekday(AddWorkDays, vbMonday) < 6 Then
> * * * * * * If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
> & _
> * * * * * * * * * * AddWorkDays & "#")) Then
> * * * * * * * * lngDayCount = lngDayCount + lngAdd
> * * * * * * End If
> * * * * End If
> * * Loop
>
> AddWorkDays_Exit:
> * * On Error GoTo 0
>
> * * Exit Function
>
> AddWorkDays_Error:
>
> * * MsgBox "Error " & Err.Number & " (" & Err.Description & _
> * * * * ") in procedure AddWorkDays of Module modDateFunctions"
> * * GoTo AddWorkDays_Exit
>
> End Function
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
>
> "gen...@yahoo.com" wrote:
> > I have been struggling with this for a few days. *Racked my brains.
> > Coming up with dead ends. *Searched through the web & groups. * It is
> > both the 'puzzle solving' and VBA looping logic I am having problems
> > with. *Can anyone give me some pointers?
>
> > I need to calculate the time it takes to complete an application,
> > accounting for:
>
> > - workday 6 am - 6 pm (clock stops during non-work hours)
> > - holidays listed in a separate table with begin & end date/times
> > (clock stops on holidays)
> > - 'events' which are in separate table related to each application and
> > have start and end date/times (clock stops until 'event' is complete)
>
> > I need to determine if the time to complete the application was within
> > a service agreement 'max time to complete'.
>
> > I have three tables:
>
> > 1. Application - has Application Start and Completed date/time fields
> > and each application has unique Application_ID
>
> > 2. Events - each event has unique Events_ID and foreign key relating
> > to each Application_ID
>
> > 3. Holidays - days that are regular holidays and specific company
> > holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
>
> > I need to determine the following:
>
> > 1. Total time to complete excluding holidays and working hours, and
> > event durations.
>
> > 2. Expected date/time of completion based on current date/time
> > excluding holidays and working hours, and event durations.
>
> > ************************************************
> > Example data:
>
> > Application Start: July 1, 2008 13:00 (clock starts)
>
> > Event 1 Start: July 1, 2008 14:00 (clock stops)
> > Event 1 End: July 2, 2008 11:00 (clock starts)
>
> > Event 2 Start: July 2, 2008 16:00 (clock stops)
> > Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
> > working hours so clock would start at next working day eg July 4 06:00
> > but July 4 was a holiday so clock starts July 5 06:00 instead)
>
> > Application Completed: July 6, 2008 13:00 (clock stops)
> > *******************************************************
>
> > My conjectured VBA so far:
>
> > ' Declare variables
> > ' Create a DAO recordset for Events grouped by Application to loop
> > through
>
> > ' Does Application have any Events?
> > If recordset has no events for application then
> > * * ' Does Application have an Application_Completion date?
> > * * If no Application_Completion date then
> > * * * * ' Calculate duration using current date (ie still open)
> > * * * * SLA_Duration = DateDiff("d" Application_Start, Now ())
> > * * Else
> > * * * * ' Calculate duration using completion date
> > * * * * SLA_Duration = DateDiff("d" Application_Start,
> > Application_Completion )
> > * * End if
> > Else ' Application has Events
> > ' Loop through events calculating
> > i = i + 1
> > 1 -- Application_Start to Event 0 Start ---> DateDiff("d"
> > Application_Start, Event 0 start)
> > 2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
> > Event_End)
> > 3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
> > +1 End, Application_Completion)
> > SLA_Duration = Sum(1 + 2 + 3)
> > Next i
> > End loop
> > End If
>
> > Where do I put in the logic that tests whether any of the dates is
> > outside of workday hours or a holiday? *Then how do I have the logic
> > 'jump ahead' to the next work day start to use that date/time instead
> > to start the clock and perform calculations?
>
> > Any pointers at all greatly appreciated!
>
> > Genoki- Hide quoted text -
>
> - Show quoted text -

Okay. I like the idea of unioning the holidays and events for calculation
purposes. Seems a clever way to simplify the caclulation routines, but keep
the entities separate for when they need to be.
--
Dave Hargis, Microsoft Access MVP


"(E-Mail Removed)" wrote:

> Thanks Klatuu & dch3,
>
> I think I have enough to solve my challenge.
>
> Klatuu your suggestion to consider Events and Holidays as logically
> the same, makes the solution simpler. However, I won't put them into
> same table but will union them and then work with them.
>
> dch3, your suggestion to break out reporting of hours into working
> hours, weekends, holidays, events helped to clarify what I need to do.
>
> I will post my solution when I work it out.
>
> Cheers,
>
> Genoki
>
> On Jul 22, 7:18 am, Klatuu <Kla...@discussions.microsoft.com> wrote:
> > Here are two functions. One that calculates the number of working days
> > between two dates. It excludes Saturdays, Sundays, and any date in the
> > holiday table. To add an event table, you could easily copy the code used to
> > count holidays. But, I would suggest that both holidays and events be in the
> > same table as they are logically the same in your case. If you need to
> > differentiate between them, add a field to the holiday table that indentifies
> > each.
> >
> > '--------------------------------------------------------------------------Â*-------------
> > ' Procedure : CalcWorkDays
> > ' DateTime : 5/8/2006 16:34
> > ' Author : Dave Hargis
> > ' Purpose : Counts the number of days between two dates excluding Saturdays,
> > ' : Sundays, and any days in the Holidays table
> > '--------------------------------------------------------------------------Â*-------------
> > '
> > Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
> >
> > On Error GoTo CalcWorkDays_Error
> >
> > 'Calculates the number of days between the dates
> > 'Add one so all days are included
> > CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
> > (DateDiff("ww", dtmStart, dtmEnd, 7) + _
> > DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
> > 'Subtract the Holidays
> > CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
> > between #" _
> > & dtmStart & "# And #" & dtmEnd & "#")
> >
> > CalcWorkDays_Exit:
> >
> > On Error Resume Next
> > Exit Function
> >
> > CalcWorkDays_Error:
> >
> > MsgBox "Error " & Err.Number & " (" & Err.Description & _
> > ") in procedure CalcWorkDays of Module modDateFunctions"
> > GoTo CalcWorkDays_Exit
> >
> > End Function
> >
> > '--------------------------------------------------------------------------Â*-------------
> > ' Procedure : AddWorkDays
> > ' DateTime : 5/8/2006 14:24
> > ' Author : Dave Hargis
> > ' Purpose : Determines the date of a work day (mon-fri) a number of days
> > after or
> > ' : before a given date.
> > ' Arguments : OriginalDate The date you want to add or subtract from to
> > determine a
> > ' : worday
> > ' : DaysToAdd Either a positive or negative number of working
> > days
> > ' : Saturdays and Sundays are not counted
> > ' : Holidays can also be omitted using a holiday table. The table
> > should
> > ' : Contain one row for each holiday. As written, the table is
> > named
> > ' : tblHolidays and the field used is a Date field named Holidate
> > ' : No time vaule should be in the Holidate field. Time values
> > may produce
> > ' : Incorrect Results
> > '--------------------------------------------------------------------------Â*-------------
> > '
> > Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
> > Dim lngAdd As Long
> > Dim lngDayCount As Long
> > Dim lngHolidayCount As Long
> >
> > On Error GoTo AddWorkDays_Error
> >
> > If DaysToAdd < 0 Then
> > lngAdd = -1
> > Else
> > lngAdd = 1
> > End If
> >
> > AddWorkDays = OriginalDate
> >
> > Do Until lngDayCount = DaysToAdd
> > AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
> > If Weekday(AddWorkDays, vbMonday) < 6 Then
> > If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
> > & _
> > AddWorkDays & "#")) Then
> > lngDayCount = lngDayCount + lngAdd
> > End If
> > End If
> > Loop
> >
> > AddWorkDays_Exit:
> > On Error GoTo 0
> >
> > Exit Function
> >
> > AddWorkDays_Error:
> >
> > MsgBox "Error " & Err.Number & " (" & Err.Description & _
> > ") in procedure AddWorkDays of Module modDateFunctions"
> > GoTo AddWorkDays_Exit
> >
> > End Function
> >
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> >
> > "gen...@yahoo.com" wrote:
> > > I have been struggling with this for a few days. Racked my brains.
> > > Coming up with dead ends. Searched through the web & groups. It is
> > > both the 'puzzle solving' and VBA looping logic I am having problems
> > > with. Can anyone give me some pointers?
> >
> > > I need to calculate the time it takes to complete an application,
> > > accounting for:
> >
> > > - workday 6 am - 6 pm (clock stops during non-work hours)
> > > - holidays listed in a separate table with begin & end date/times
> > > (clock stops on holidays)
> > > - 'events' which are in separate table related to each application and
> > > have start and end date/times (clock stops until 'event' is complete)
> >
> > > I need to determine if the time to complete the application was within
> > > a service agreement 'max time to complete'.
> >
> > > I have three tables:
> >
> > > 1. Application - has Application Start and Completed date/time fields
> > > and each application has unique Application_ID
> >
> > > 2. Events - each event has unique Events_ID and foreign key relating
> > > to each Application_ID
> >
> > > 3. Holidays - days that are regular holidays and specific company
> > > holidays with Holidays_Date, Holidays_BeginHours, Holidays_EndHours
> >
> > > I need to determine the following:
> >
> > > 1. Total time to complete excluding holidays and working hours, and
> > > event durations.
> >
> > > 2. Expected date/time of completion based on current date/time
> > > excluding holidays and working hours, and event durations.
> >
> > > ************************************************
> > > Example data:
> >
> > > Application Start: July 1, 2008 13:00 (clock starts)
> >
> > > Event 1 Start: July 1, 2008 14:00 (clock stops)
> > > Event 1 End: July 2, 2008 11:00 (clock starts)
> >
> > > Event 2 Start: July 2, 2008 16:00 (clock stops)
> > > Event 2 End: July 4, 2008 19:00 (Note: Event ends out of regular
> > > working hours so clock would start at next working day eg July 4 06:00
> > > but July 4 was a holiday so clock starts July 5 06:00 instead)
> >
> > > Application Completed: July 6, 2008 13:00 (clock stops)
> > > *******************************************************
> >
> > > My conjectured VBA so far:
> >
> > > ' Declare variables
> > > ' Create a DAO recordset for Events grouped by Application to loop
> > > through
> >
> > > ' Does Application have any Events?
> > > If recordset has no events for application then
> > > ' Does Application have an Application_Completion date?
> > > If no Application_Completion date then
> > > ' Calculate duration using current date (ie still open)
> > > SLA_Duration = DateDiff("d" Application_Start, Now ())
> > > Else
> > > ' Calculate duration using completion date
> > > SLA_Duration = DateDiff("d" Application_Start,
> > > Application_Completion )
> > > End if
> > > Else ' Application has Events
> > > ' Loop through events calculating
> > > i = i + 1
> > > 1 -- Application_Start to Event 0 Start ---> DateDiff("d"
> > > Application_Start, Event 0 start)
> > > 2 -- Event 0 to Event n+1, etc ---> DateDiff("d" Event_Start,
> > > Event_End)
> > > 3 -- Event n+1 End to Application_Completion ---> DateDiff("d" Event n
> > > +1 End, Application_Completion)
> > > SLA_Duration = Sum(1 + 2 + 3)
> > > Next i
> > > End loop
> > > End If
> >
> > > Where do I put in the logic that tests whether any of the dates is
> > > outside of workday hours or a holiday? Then how do I have the logic
> > > 'jump ahead' to the next work day start to use that date/time instead
> > > to start the clock and perform calculations?
> >
> > > Any pointers at all greatly appreciated!
> >
> > > Genoki- Hide quoted text -
> >
> > - Show quoted text -
>
>