I need help - an earlier post received no solution

[TraciAnnNeedsHelp]

This may be a bit too challenging or maybe even impossible. Especially for a
newbie like me. If anyone is willing to help me tackle this I would be
extremely grateful.

Here is my original post on 3/3/2009:

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime]: Date/Time (UTC) when ticket is created
- [SLAType]: 1 = 24hr; 2 = 4hr
- [EventID]: 7 = Onsite Service
- [EventTime]: Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

 

[Clif McIrvin]

Hi TraciAnn -

I have no experience with this type of analysis; but it looks to be in
some respects similiar to discussions I have seen in these groups
regarding date calculations taking into account the company's holiday
schedule.

I'd suggest that you go to google groups and search for discussions
involving time, date or holiday calculations. There have also been
pointers offered in past discussions regarding how to get the best use
of google groups searches.

If you have trouble with searching the newsgroup archives post back
here, and I'm pretty certain someone will be able to assist.

HTH!
--
Clif

This may be a bit too challenging or maybe even impossible. Especially
for a
newbie like me. If anyone is willing to help me tackle this I would be
extremely grateful.

Here is my original post on 3/3/2009:

I need to start monitoring my company's response time when a customer
calls
for help. I already have a database that stores the data for the calls
and
the responses but I don't have a way of calculating the results.
Mainly
because the complexity of the difference between a stored value
[CreateTime]
and the variable I need to calculate [StartSLA] which takes into
account
non-business hours.

I have built a query using 2 tables which have the following fields
used in
the query:
- [CreateTime]: Date/Time (UTC) when ticket is created
- [SLAType]: 1 = 24hr; 2 = 4hr
- [EventID]: 7 = Onsite Service
- [EventTime]: Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based
on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous
business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business
day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current
business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current
business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous
business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00
(current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help
is
greatly appreciated.

Gratefully,
TraciAnn

 

[TraciAnnNeedsHelp]

Clif,

I respect the time that MVP's put into the questions posted in this forum
and try not to take advantage of them. I always perform a variety of searches
on my problem before I post the question. When previous posts look similar to
what I am looking for I try it and then maybe make some changes to it to see
if I can at least figure it out first. That is how I learn best.

In this instance, I have seen questions related to this but not providing
the assistance I needed. So, I posted my question. I never imagined I would
be told to go look somewhere else. Maybe I misunderstand the purpose of this
discussion group.

Sincerely,
TraciAnn

Hi TraciAnn -

I have no experience with this type of analysis; but it looks to be in
some respects similiar to discussions I have seen in these groups
regarding date calculations taking into account the company's holiday
schedule.

I'd suggest that you go to google groups and search for discussions
involving time, date or holiday calculations. There have also been
pointers offered in past discussions regarding how to get the best use
of google groups searches.

If you have trouble with searching the newsgroup archives post back
here, and I'm pretty certain someone will be able to assist.

HTH!
--
Clif

This may be a bit too challenging or maybe even impossible. Especially
for a
newbie like me. If anyone is willing to help me tackle this I would be
extremely grateful.

Here is my original post on 3/3/2009:

I need to start monitoring my company's response time when a customer
calls
for help. I already have a database that stores the data for the calls
and
the responses but I don't have a way of calculating the results.
Mainly
because the complexity of the difference between a stored value
[CreateTime]
and the variable I need to calculate [StartSLA] which takes into
account
non-business hours.

I have built a query using 2 tables which have the following fields
used in
the query:
- [CreateTime]: Date/Time (UTC) when ticket is created
- [SLAType]: 1 = 24hr; 2 = 4hr
- [EventID]: 7 = Onsite Service
- [EventTime]: Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based
on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous
business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business
day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current
business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current
business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous
business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00
(current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help
is
greatly appreciated.

Gratefully,
TraciAnn

 

[Jeff Boyce]

TraciAnn

These newsgroups are "staffed" by volunteers. If you don't receive a
response, there may be no one who understands your situation, or no one
available, or no one ... It's only been two days, so if your need is
urgent, by all means check into finding someone you could hire.

I regularly remind folks that the on-line searches can return many more
possibilities than searching only within the newsgroups. For example, the
UtterAccess 'groups aren't reflected in what you find here, but are in what
you can find via on-line search.

My first response in looking over your post is that you appear to be
describing a lot of business rules, but not much about the underlying data
structure & data types. "How" depends on "what".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michael Gramelspacher]

This may be a bit too challenging or maybe even impossible. Especially for a
newbie like me. If anyone is willing to help me tackle this I would be
extremely grateful.

Here is my original post on 3/3/2009:

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime]: Date/Time (UTC) when ticket is created
- [SLAType]: 1 = 24hr; 2 = 4hr
- [EventID]: 7 = Onsite Service
- [EventTime]: Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

I really do not know if this is any help, but here goes.

The function calculates business days between two dates. There also hace to be a table tblHolidays
with one column Holidate with type DATETIME.

'---------------------------------------------------------------------------------------
' 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

This is my function which calculates the work minutes between two dates.
You can try it and see if it works. Hopefully it works, but maybe not.


Function WorkMinutesBetween(start_date As Date, _
end_date As Date, _
Optional start_time As Date, _
Optional end_time As Date) As Long

' start_date is the beginning date time value
' end_date is the ending date time value
' start_time is the time of day the work day begins
' end_time is the time of day the work day ends

' assumptions are that start_date and end_date are workdays
' and start_date is earlier than end_date

' Returns work hours: (includes lunch time)
'?WorkMinutesBetween(#1/23/2009 7:28:56 PM#, #1/26/2009 12:14:30 PM#,
'#8:00:00 AM#, #5:00:00 PM#)
'254
'?Right$("00" &(254 \ 60),2) & ":" & Right$("00" & (254 Mod 60),2)
'04:14

'?WorkMinutesBetween(#1/23/2009 1:28:56 PM#, #1/26/2009 12:14:30 PM#,
'#8:00:00 AM#, #5:00:00 PM#)
'466
'?Right$("00" &(466 \ 60),2) & ":" & Right$("00" & (466 Mod 60),2)
'07:46

'?WorkMinutesBetween(#1/23/2009 7:28:56 PM#, #1/26/2009 17:14:30 PM#,
'#8:00:00 AM#, #5:00:00 PM#)
'540
'?Right$("00" &(540 \ 60),2) & ":" & Right$("00" & (540 Mod 60),2)
'09:00

' if no work day begin then assume this
If start_time = #12:00:00 AM# Then start_time = #8:00:00 AM#
' if no work day end then assume this
If end_time = #12:00:00 AM# Then end_time = #5:00:00 PM#

'minutes between period start and business open time
'Debug.Print DateDiff("n", start_time, _
IIf(TimeValue(start_date) < start_time, start_time, _
IIf(TimeValue(start_date) > end_time, end_time, TimeValue(start_date))))

' minutes between period stop and business close time
'Debug.Print DateDiff("n", _
IIf(TimeValue(end_date) > end_time, end_time, _
IIf(TimeValue(end_date) < start_time, start_time, TimeValue(end_date))), end_time)

WorkMinutesBetween = CalcWorkDays(DateValue(start_date), _
DateValue(end_date)) * _
DateDiff("n", start_time, end_time) - _
((DateDiff("n", start_time, _
IIf(TimeValue(start_date) < start_time, start_time, _
IIf(TimeValue(start_date) > end_time, end_time, TimeValue(start_date)))) + _
(DateDiff("n", _
IIf(TimeValue(end_date) > end_time, end_time, _
IIf(TimeValue(end_date) < start_time, start_time, TimeValue(end_date))), end_time))))

End Function

 

[Clif McIrvin]

TraciAnn,

I apologise for sounding like I was trying to send you away, that
certainly was not my intent.

I had one specific discussion in mind regarding date calculations that
depended heavily on non-predictable (that is, impossible to write into a
function algorithm) client specific non-business days that I thought
seemed to involve similiar issues to your question. It seems to me that
Pete Cresswell was either asking the question, or at least was involved
in the discussion. Does this sound familiar? If you havn't seen that
discussion I'd be happy to see if I can locate it and post a link.

I've received so much help from participants in this discussion group
(not all from MVPs) that even though I'm far from claiming to be an
expert in Access I try to chip in and share the load when I see a
question that I think I can help with. In this case, the only help I
felt I had to offer was the memory of a prior discussion.

One thing I've never quite figured out is how other respondents in these
forums "discover" posts to respond to ... somehow I suspect that very
few people read all the posts. I'm guessing that there are filtering
rules out there, that look for key words in subject lines - so as a
suggestion - if there are no other replies to your inquiry within a few
days try rewording your subject line to indicate the nature of your
question and re-post again.

It appeared to me that you did a good job of presenting your question --
It seems to me that you do have a reasonable understanding of the
purpose of this newsgroup.

Again, I'm sorry I sounded as though I was trying to discourage you.

 

[Beetle]

TraciAnn,

Clif wasn't trying to tell you to go away and look elsewhere, he was just
trying to offer whatever advice he could. Unless an MVP, or someone who
happens to know the specific answer you're looking for decides to jump
in on the thread, the rest of us mere mortals can only offer whatever we
think might be helpful.

I don't know if I can help solve your issue or not, but I can tell you it
certainly can be done. However, it would be helpful to know more of
the specifics. For example, when do you want this process to happen?
Should it happen during the initial data entry process, or sometime later
when a manager or someone else updates the original record? Or perhaps
it is a process you want the application to run at a certain time of day?

Also, will any data entry (or at least this specific process) ever happen
on a non-working day or "holiday", or is your business closed completely
on those days? In other words, will this process always happen on the
"current business day", or could happen on some other day and you
need to determine what the value for "current business day" should be?

It may be something as simple as;

Select Case [SLAType]
Case 1
If [CreateTime] < Date() & " 12:00" Then
[StartSLA] = Date() & " 12:00"
Else
[StartSLA] = Date() & " 16:00"
End If
Case 2
If [CreateTime] < Date() & " 8:00" Then
[StartSLA] = Date() & " 8:00"
Else
[StartSLA] = [CreateTime]
End If
End Select

Or, it may be more complicated than that, depending on the circumstances.

--
_________

Sean Bailey

Clif,

I respect the time that MVP's put into the questions posted in this forum
and try not to take advantage of them. I always perform a variety of searches
on my problem before I post the question. When previous posts look similar to
what I am looking for I try it and then maybe make some changes to it to see
if I can at least figure it out first. That is how I learn best.

In this instance, I have seen questions related to this but not providing
the assistance I needed. So, I posted my question. I never imagined I would
be told to go look somewhere else. Maybe I misunderstand the purpose of this
discussion group.

Sincerely,
TraciAnn

Hi TraciAnn -

I have no experience with this type of analysis; but it looks to be in
some respects similiar to discussions I have seen in these groups
regarding date calculations taking into account the company's holiday
schedule.

I'd suggest that you go to google groups and search for discussions
involving time, date or holiday calculations. There have also been
pointers offered in past discussions regarding how to get the best use
of google groups searches.

If you have trouble with searching the newsgroup archives post back
here, and I'm pretty certain someone will be able to assist.

HTH!
--
Clif

This may be a bit too challenging or maybe even impossible. Especially
for a
newbie like me. If anyone is willing to help me tackle this I would be
extremely grateful.

Here is my original post on 3/3/2009:

I need to start monitoring my company's response time when a customer
calls
for help. I already have a database that stores the data for the calls
and
the responses but I don't have a way of calculating the results.
Mainly
because the complexity of the difference between a stored value
[CreateTime]
and the variable I need to calculate [StartSLA] which takes into
account
non-business hours.

I have built a query using 2 tables which have the following fields
used in
the query:
- [CreateTime]: Date/Time (UTC) when ticket is created
- [SLAType]: 1 = 24hr; 2 = 4hr
- [EventID]: 7 = Onsite Service
- [EventTime]: Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based
on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous
business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business
day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current
business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current
business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous
business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00
(current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help
is
greatly appreciated.

Gratefully,
TraciAnn

 

[TraciAnnNeedsHelp]

Yes. I understand. Everyone on here has been extremely helpful. I've been
assigned by my supervisors sometimes what seems to be an impossible task. It
is a great opportunity for my advancement, but it can also be a "deal
breaker" for me. I think I'm quite capable of learning what is necessary.
Time isn't necessarily on my side and causes me to be a bit impatient (I'm
sorry). So many things need tackled in a short period of time and that means
I'm relying on the generosity of others. I don't like to do that.

To answer your question regarding "when do I want this process to happen"?

This query is for a managerial report that will be run "at will" to see how
well the department is meeting their SLA for a specific client. The raw data
is in an SQL database that can only be queried from Access (the data in the
tables cannot be changed). Therefore, the "process" of calculating SLA Start
times needs to be accomplished randomly, each time the query is run.

Yes, the "CreateDate" may happen during non-business hours. In stating that,
it is important to keep in mind that sometimes the "CreateDate" can also be
during business hours but the StartSLA would still need adjusted.

Clif, Beetle, Jeff, Michael, Clifford, etc. You are ALL wonderful. I don't
take for granted the advice you offer.

Sincerely,
TraciAnn

TraciAnn,

Clif wasn't trying to tell you to go away and look elsewhere, he was just
trying to offer whatever advice he could. Unless an MVP, or someone who
happens to know the specific answer you're looking for decides to jump
in on the thread, the rest of us mere mortals can only offer whatever we
think might be helpful.

I don't know if I can help solve your issue or not, but I can tell you it
certainly can be done. However, it would be helpful to know more of
the specifics. For example, when do you want this process to happen?
Should it happen during the initial data entry process, or sometime later
when a manager or someone else updates the original record? Or perhaps
it is a process you want the application to run at a certain time of day?

Also, will any data entry (or at least this specific process) ever happen
on a non-working day or "holiday", or is your business closed completely
on those days? In other words, will this process always happen on the
"current business day", or could happen on some other day and you
need to determine what the value for "current business day" should be?

It may be something as simple as;

Select Case [SLAType]
Case 1
If [CreateTime] < Date() & " 12:00" Then
[StartSLA] = Date() & " 12:00"
Else
[StartSLA] = Date() & " 16:00"
End If
Case 2
If [CreateTime] < Date() & " 8:00" Then
[StartSLA] = Date() & " 8:00"
Else
[StartSLA] = [CreateTime]
End If
End Select

Or, it may be more complicated than that, depending on the circumstances.

--
_________

Sean Bailey

Clif,

I respect the time that MVP's put into the questions posted in this forum
and try not to take advantage of them. I always perform a variety of searches
on my problem before I post the question. When previous posts look similar to
what I am looking for I try it and then maybe make some changes to it to see
if I can at least figure it out first. That is how I learn best.

In this instance, I have seen questions related to this but not providing
the assistance I needed. So, I posted my question. I never imagined I would
be told to go look somewhere else. Maybe I misunderstand the purpose of this
discussion group.

Sincerely,
TraciAnn

Hi TraciAnn -

I have no experience with this type of analysis; but it looks to be in
some respects similiar to discussions I have seen in these groups
regarding date calculations taking into account the company's holiday
schedule.

I'd suggest that you go to google groups and search for discussions
involving time, date or holiday calculations. There have also been
pointers offered in past discussions regarding how to get the best use
of google groups searches.

If you have trouble with searching the newsgroup archives post back
here, and I'm pretty certain someone will be able to assist.

HTH!
--
Clif

in message This may be a bit too challenging or maybe even impossible. Especially
for a
newbie like me. If anyone is willing to help me tackle this I would be
extremely grateful.

Here is my original post on 3/3/2009:

I need to start monitoring my company's response time when a customer
calls
for help. I already have a database that stores the data for the calls
and
the responses but I don't have a way of calculating the results.
Mainly
because the complexity of the difference between a stored value
[CreateTime]
and the variable I need to calculate [StartSLA] which takes into
account
non-business hours.

I have built a query using 2 tables which have the following fields
used in
the query:
- [CreateTime]: Date/Time (UTC) when ticket is created
- [SLAType]: 1 = 24hr; 2 = 4hr
- [EventID]: 7 = Onsite Service
- [EventTime]: Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based
on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous
business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business
day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current
business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current
business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous
business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00
(current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help
is
greatly appreciated.

Gratefully,
TraciAnn

 

[TraciAnnNeedsHelp]

Michael,

I appreciate your response. I don't know enough about VBA to know whether
this will work or not. I know enough to cut and paste this into a module but
that would be about it.

From my uneducated inspection though, I would have to assume it doesn't meet
my need because I first need to identify specific StartSLA times based on the
CreateDate(/Time) of a record. Although the code you provided deals with
identified holidays and actual business times. It doesn't provide the detail
I need to tailor it to my specific requirements.

Thank you again for your time.
TraciAnn

This may be a bit too challenging or maybe even impossible. Especially for a
newbie like me. If anyone is willing to help me tackle this I would be
extremely grateful.

Here is my original post on 3/3/2009:

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime]: Date/Time (UTC) when ticket is created
- [SLAType]: 1 = 24hr; 2 = 4hr
- [EventID]: 7 = Onsite Service
- [EventTime]: Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

I really do not know if this is any help, but here goes.

The function calculates business days between two dates. There also hace to be a table tblHolidays
with one column Holidate with type DATETIME.

'---------------------------------------------------------------------------------------
' 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

This is my function which calculates the work minutes between two dates.
You can try it and see if it works. Hopefully it works, but maybe not.


Function WorkMinutesBetween(start_date As Date, _
end_date As Date, _
Optional start_time As Date, _
Optional end_time As Date) As Long

' start_date is the beginning date time value
' end_date is the ending date time value
' start_time is the time of day the work day begins
' end_time is the time of day the work day ends

' assumptions are that start_date and end_date are workdays
' and start_date is earlier than end_date

' Returns work hours: (includes lunch time)
'?WorkMinutesBetween(#1/23/2009 7:28:56 PM#, #1/26/2009 12:14:30 PM#,
'#8:00:00 AM#, #5:00:00 PM#)
'254
'?Right$("00" &(254 \ 60),2) & ":" & Right$("00" & (254 Mod 60),2)
'04:14

'?WorkMinutesBetween(#1/23/2009 1:28:56 PM#, #1/26/2009 12:14:30 PM#,
'#8:00:00 AM#, #5:00:00 PM#)
'466
'?Right$("00" &(466 \ 60),2) & ":" & Right$("00" & (466 Mod 60),2)
'07:46

'?WorkMinutesBetween(#1/23/2009 7:28:56 PM#, #1/26/2009 17:14:30 PM#,
'#8:00:00 AM#, #5:00:00 PM#)
'540
'?Right$("00" &(540 \ 60),2) & ":" & Right$("00" & (540 Mod 60),2)
'09:00

' if no work day begin then assume this
If start_time = #12:00:00 AM# Then start_time = #8:00:00 AM#
' if no work day end then assume this
If end_time = #12:00:00 AM# Then end_time = #5:00:00 PM#

'minutes between period start and business open time
'Debug.Print DateDiff("n", start_time, _
IIf(TimeValue(start_date) < start_time, start_time, _
IIf(TimeValue(start_date) > end_time, end_time, TimeValue(start_date))))

' minutes between period stop and business close time
'Debug.Print DateDiff("n", _
IIf(TimeValue(end_date) > end_time, end_time, _
IIf(TimeValue(end_date) < start_time, start_time, TimeValue(end_date))), end_time)

WorkMinutesBetween = CalcWorkDays(DateValue(start_date), _
DateValue(end_date)) * _
DateDiff("n", start_time, end_time) - _
((DateDiff("n", start_time, _
IIf(TimeValue(start_date) < start_time, start_time, _
IIf(TimeValue(start_date) > end_time, end_time, TimeValue(start_date)))) + _
(DateDiff("n", _
IIf(TimeValue(end_date) > end_time, end_time, _
IIf(TimeValue(end_date) < start_time, start_time, TimeValue(end_date))), end_time))))

End Function

 

[Michael Gramelspacher]

Michael,

I appreciate your response. I don't know enough about VBA to know whether
this will work or not. I know enough to cut and paste this into a module but
that would be about it.

From my uneducated inspection though, I would have to assume it doesn't meet
my need because I first need to identify specific StartSLA times based on the
CreateDate(/Time) of a record. Although the code you provided deals with
identified holidays and actual business times. It doesn't provide the detail
I need to tailor it to my specific requirements.

Thank you again for your time.
TraciAnn

Called received: #3/6/2009 11:37:00 AM#

Response provided: #3/9/2009 09:44:30#

Total business minutes: 486
Total hours: 08:06

?WorkMinutesBetween(#3/6/2009 11:37:00 AM#, #3/9/2009 09:44:30#, #8:00:00#, #17:59:59#)
486
?Right$("00" &(486 \ 60),2) & ":" & Right$("00" & (486 Mod 60),2)
08:06

This is all the function does. You need to apply your rules to determine your date/times and
business hours and plug them into the function.

It seems in one case you need to adjust start date to 12:00:00 on next business date. You probably
need a calendar table to come up with the next business date.

 

[TraciAnnNeedsHelp]

Michael,

Again, thank you for your time. Your function works great for determining
the amount of time between two date/times.

However, that isn't my challenge. It is much more difficult typing what it
is that I know in my head needs to happen.

My challenge is how to determine the beginning date/time value given the
variables of Date/Time when the ticket is entered compared to non-business
hours and adjusted times during business hours.

If the CreatedDate occurs during certain business hours it is also the
Date/Time the SLA should start.

If the CreatedDate occurs during other business hours it gets "rounded" to
the next "reasonable" SLA start time.

If the CreatedDate occurs during non-business hours it rolls forward to the
next business day.

Once I have the correct SLA Start time it will not be a challenge to
calculate the difference between SLA Start time and Response time.

Thanks Again Michael!!!
TraciAnn

 

[Clif McIrvin]

TraciAnn - I've been away from the computer today --- just 'catching up'
now --

Is this the same problem that Clifford offered to help you with? If so,
I'm pretty certain the answers to these questions will be important to
him ....

Regarding 'certain business hours', 'other business hours' and 'next
business day':

Specifically what are these 'certain' and 'other' business hours?
Are they the same for all business days?
Are they the same for all customers?

What about 'next business day'? Is Saturday ever a business day?
Is a Monday, Tuesday, Wednesday, ... ever *not* a business day?

I think you have mentioned elsewhere that the data resides in a SQL
server backend ... do you have the capability of building any necessary
reference tables in an Access (Jet) backend? For instance, a table to
define your 'certain' and 'other' business hours; and a table to define
non-business days.

 

[Clif McIrvin]

Apology accepted. I'm pretty sure we all understand the pressure of
being 'under the gun.'

Anytime I think I have something useful to contribute I'll chime back
in.

--
Clif

Yes. I understand. Everyone on here has been extremely helpful. I've
been
assigned by my supervisors sometimes what seems to be an impossible
task. It
is a great opportunity for my advancement, but it can also be a "deal
breaker" for me. I think I'm quite capable of learning what is
necessary.
Time isn't necessarily on my side and causes me to be a bit impatient
(I'm
sorry). So many things need tackled in a short period of time and that
means
I'm relying on the generosity of others. I don't like to do that.

<...>

 

[Michael Gramelspacher]

If the CreatedDate occurs during non-business hours it rolls forward to the
next business day.

Taking just that statement, my question is, how do you determine the next business day?

I know how I determine the next business day; I have a calendar table with all dates and a column
for IsBusinessDay. Business days have a sequential number, so the next day is the current date + 1.

Google for function GetBusinessDay by Arvin Meyer. there may be others out there.
This function uses a tblHolidays same as the CalcWorkDays function by Dave Hargis.
I edited the column name HoliDate and made it HolidayDate to agree with Meyer's function.

This is not pretty, but I think it may do what you want. I will leave it to you to run with it from
here.


Function GetMinutes(CreateTime As Date, _
EventTime As Date, _
SLAType As Integer) As Integer

' this uses GetBusinessDay function by Arvin Meyer

GetMinutes = IIf([SLAType] = 1 And TimeValue([CreateTime]) > #3:59:59 PM#, _
WorkMinutesBetween(GetBusinessDay(DateValue([CreateTime]), 1) + #12:00:00 PM#, _
[EventTime], #8:00:00 AM#, #3:59:59 PM#), IIf([SLAType] = 1 And _
TimeValue([CreateTime]) > #12:00:00 PM# And _
TimeValue([CreateTime]) < #4:00:00 PM#, _
WorkMinutesBetween(DateValue([CreateTime]) + #4:00:00 PM#, _
[EventTime], #8:00:00 AM#, #3:59:59 PM#), IIf([SLAType] = 1 _
And TimeValue([CreateTime]) <= #12:00:00 PM#, _
WorkMinutesBetween(DateValue([CreateTime]) + #12:00:00 PM#, _
[EventTime], #8:00:00 AM#, #3:59:59 PM#), WorkMinutesBetween([CreateTime], _
[EventTime], #8:00:00 AM#, #5:59:59 PM#))))

End Function

Sub TestGetMinutes()

Dim ctime As Date
Dim etime As Date
Dim sla As Integer
Dim gminutes As Integer

ctime = #3/6/2009 9:45:23 AM#
etime = #3/9/2009 1:15:20 PM#
sla = 1

gminutes = GetMinutes(ctime, etime, sla)

MsgBox Right$("00" & (gminutes \ 60), 2) & ":" & Right$("00" & (gminutes Mod 60), 2)

End Sub

 

[TraciAnnNeedsHelp]

Yesterday I was running Payroll, which keeps me busy the entire day so I was
unable to respond. Believe me; it wasn’t because this issue isn’t important.

Is this the same problem that Clifford offered to help you with? If so,
I'm pretty certain the answers to these questions will be important to
him ....

Yes. Clifford offered to help in the original post but he has not responded
to my previous answer for several days. He said he had to give it more
thought. If/When he does reply again, and if necessary, I was going to direct
him to this post since it is the one that has the activity with allot of
clarification.

Regarding 'certain business hours', 'other business hours' and 'next
business day':

Specifically what are these 'certain' and 'other' business hours?
Are they the same for all business days? Yes.
Are they the same for all customers?

Yes.

These were stated in the first post but maybe clarification is necessary:

“Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidaysâ€

1. 4hr SLA Business Hours: Monday – Friday 8:00am – 6:00pm
- Except “Government†Holidays (Memorial Day, Labor Day, Thanksgiving,
etc.)
2. NBD SLA Business Hours: Monday – Friday 8:00am – 4:00pm
- Except “Government†Holidays (Memorial Day, Labor Day, Thanksgiving,
etc.)

What about 'next business day'? Is Saturday ever a business day?

No. Saturdays are never a business day

Is a Monday, Tuesday, Wednesday, ... ever *not* a business day?

Yes. When a weekday is a Government Holiday, it is NOT a business day.

I think you have mentioned elsewhere that the data resides in a SQL
server backend ... do you have the capability of building any necessary
reference tables in an Access (Jet) backend? For instance, a table to
define your 'certain' and 'other' business hours; and a table to define
non-business days.

“Capable†(lol), I do have a couple Access tables that I use to store
information that the SQL tables don’t use. I use queries to create links
between the SQL tables and my (Access) tables in order to produce necessary
reports.

So, yes, I can create additional tables, I just lack the know how to utilize
them once they are created. (I’m getting better though, thanks to people like
you!!)

Thanks Clif!!!
TraciAnn

 

[Clif McIrvin]

in message

So, yes, I can create additional tables, I just lack the know how to
utilize
them once they are created. (I'm getting better though, thanks to
people like
you!!)

Thanks Clif!!!
TraciAnn

Believe me; I understand "Busy"! One of my great frustrations is that
simply keeping up with my job leaves me (nil) time to develop the
application that when completed will make the job more efficient. Arghh.

For now, I'm mostly just watching this thread; it seems others have
posted tools that can build what you need .... I suspect that's what
Clifford is working towards.

A year ago I don't think I would have had the knowledge and comfort
level with Access to tackle the problem you are describing .... so stick
with it! The assistance from the members of this forum is incredible,
and there is light at the end of the tunnel - it mostly requires time
and effort and you show the willingness to learn <grin>.

Hang in there, it does get easier the higher you make it up the learning
curve!

 

[TraciAnnNeedsHelp]

Clif,

That is the most comforting thing I have heard in a long time. I appreciate
the information and I do desire to be a solution provider for this forum
instead of a requester. I have been researching books that will help me get
from "table & query builder" to a true "application developer" but I can't
find any that walk me through the process rather than tell me what all the
different features do.

As an MCT and MOS Expert I have plenty of foundation. I just need the
step-by-step or "Hands On" approach like what this newsgroup gives me. I am
SOOOOO.... thankful for everyone on here and there willingness to help.

TraciAnn

 

[Clif McIrvin]

TraciAnn,

I'm glad I sent that reply then .

Are you already famiuliar with these links? There's a *lot* of really
good information out there ... it just takes time to work through it.

(Thanks to John W. Vinson [MVP] for this info)
Here are some tutorials and other resources that you should find
helpful:
A free tutorial written by Crystal (MS Access MVP):
http://www.accessmvp.com/Strive4Peace/Index.htm
also at http://allenbrowne.com/casu-22.htmlMVP
Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
Here's a primer with 23 well defined, well written, clearly named
chapters:
http://www.functionx.com/vbaccess/index.htm
The Access Web resources page:
http://www.mvps.org/access/resources/index.html
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

 

[Larry Linson]

I have additional Access resources in a list at
http://sp.ntpcug.org/accesssig/default.aspx which you may find helpful.

Larry Linson
Microsoft Office Access MVP

 

[TraciAnnNeedsHelp]

This information looks exactly like what I need. I know what I'll be doing
for the next few weekends

I wish I had the freedom to study during the day; I would get there so much
quicker. Until then I am going to have to rely on you guys for the answers
that will please my bosses.

This is an awesome community. Everyone is so helpful.

Thanks Clif!