Calculate time lapse with exclusions

K

kmacd6951

Board Regular
Joined
Mar 3, 2004
Messages
58
I found post in the Excel forum that address this, but looking for an Access solution.

Right now I am calculating the difference in time (CRT) between when a call came in and when someone closed (or responded) to that call.

DateDiff("n", [ADATECREATE], [ADATERESOLVED])

I would like to take this a step further however and take business hours into consideration. So, if someone calls after 8PM Friday and before 8AM Monday the CRT doesn't look so horrible.
Ideally if they called Friday at 10:00PM and we responded on Monday at 8:45AM, the CRT should only be 45 mins (difference from when we began business again and when we closed the call).

Also the format of my ADATECREATE and ADATERESOLVED fields is:
for example: 04/16/2004 4:16:31 PM

Has anyone created a Module already that does something like this? So I don't have to start from scratch?

Thanks
Katie
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Take a look at MS KB article #210064 - Determine If a Date Falls on a Weekend or on a Holiday in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;210064&Product=acc2000

and you can try:

=DateDiff("ww",[StartDate],[EndDate],[DayOfWeek])-Int([DayOfWeek]-Weekday([StartDate]))

or
Code: 
'Calculate Work Days ONLY - No Weekends!:

Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = (NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate)) + 1
End If
End Function
Then, in the form, query, or report where you need to do this calculation, use the
following code:

dim intdate as integer
intDays = DateDiffW(varBegDate, varEndDate) 'get total number of weekdays
Me.txtTotDays = intDays 'put in total weekdays out

This should get you started. However, doing the calculation as you have proposed may be difficult as you would also need to setup "working hours" for business days.

HTH,
CT
 
Upvote 0
I might not be following you completely... I tried using that code, then in my query, I have the following expression:

Response Time: DateDiffW([ADATECREATED],[ADATECLOSED])

This returns 0,1 etc.... but I am needing the differences in hh:nn

This was a post I found where someone in Excel land was trying to do the same thing... just not sure how Excel code translates to Access code.

http://www.mrexcel.com/board2/viewtopic.php?t=20483&highlight=
 
Upvote 0
You can use that excel code directly in access with no changes. Should work fine! I tried it and have made a change to the formatting. Below is my code. All you need to do is paste this into a module in Access and compile. To test...go to the immediate window and type something like ? nhw("4/21/2004 9:00:00 AM","4/21/2004 1:01:10 PM","09:00","17:00",0)
the result is....
1
04:01:00

Code: 
Option Compare Database
Option Explicit

Function nhw(StartingDateTime As Date, EndingDateTime As Date, HourStart As Date, HourEnd As Date, ReturnType As Integer) As Variant

        

        Dim TotalNetHoursWorked

        Dim ThisDayEnd As Date

        Dim ThisDayBegin As Date

        Dim Cntr As Integer



If StartingDateTime = 0 Then Exit Function

If EndingDateTime = 0 Then Exit Function



If Format(StartingDateTime, "DDDD") = "Friday" And Hour(StartingDateTime) > 17 Then

    StartingDateTime = Format(DateAdd("d", 3, StartingDateTime), "MM/DD/YY") & " 09:00 AM"

ElseIf Format(StartingDateTime, "DDDD") = "Saturday" Then

    StartingDateTime = Format(DateAdd("d", 2, StartingDateTime), "MM/DD/YY") & " 09:00 AM"

ElseIf Format(StartingDateTime, "DDDD") = "Sunday" Then

    StartingDateTime = Format(DateAdd("d", 1, StartingDateTime), "MM/DD/YY") & " 09:00 AM"

End If



If DateDiff("d", StartingDateTime, EndingDateTime) > 1826 Then

    MsgBox "Out of range - Greater than five years."

    nhw = "Invalid Time"

    Exit Function

End If



ThisDayEnd = Month(StartingDateTime) & "/" & Day(StartingDateTime) & "/" & _
Year(StartingDateTime)

ThisDayEnd = ThisDayEnd + HourEnd



If ThisDayEnd > EndingDateTime Then

    TotalNetHoursWorked = DateDiff("n", StartingDateTime, EndingDateTime)

    GoTo AllDone

End If



TotalNetHoursWorked = DateDiff("n", StartingDateTime, ThisDayEnd)



For Cntr = 1 To 1826

    ThisDayBegin = Month(StartingDateTime + Cntr) & "/" & Day(StartingDateTime + Cntr) & "/" & _
Year(StartingDateTime + Cntr) & Format(HourStart, " HH:MM")

    ThisDayEnd = ThisDayBegin + (HourEnd - HourStart)

    If ThisDayBegin > EndingDateTime Then Exit For

    If ThisDayEnd > EndingDateTime Then

        If Format(ThisDayEnd, "MMMM") = "Saturday" Then Exit For

        TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, EndingDateTime)

        Exit For

    End If

    If Format(ThisDayEnd, "DDDD") <> "Saturday" And _
Format(ThisDayEnd, "DDDD") <> "Sunday" Then

    TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, ThisDayEnd)

    End If

Next



AllDone:

Debug.Print TotalNetHoursWorked Mod 60

'*Modified by CT -removed this check
'If TotalNetHoursWorked > 1440 Then

'*Modified by CT -Added Formatting around hours and minutes
    nhw = Format(Int(TotalNetHoursWorked / 60), "00") & ":" & Format(TotalNetHoursWorked Mod 60, "00") & ":00"

'*Modified by CT -removed this check
'Else: nhw = 0.000694444 * TotalNetHoursWorked

'*Modified by CT -removed this check
'End If

End Function

HTH,
CT
 
Upvote 0
WOW!!! Thank you SOOOOO much!

This is looking great. Can I ask you one more question? For the last argument in the nhw function, called "ReturnType As Integer", what is that? How do you know that should a "0" rather than a "1" for example?

I REALLY appreciate your help!

Katie
 
Upvote 0
I took a look and for this function, that argument is useless, so you can eliminate it. I imagine it might have been used for returning the value as a whole number for another calculation.

Also, I've made another code "enhancement" replace this code:

Code: 
If Format(StartingDateTime, "DDDD") = "Friday" And Hour(StartingDateTime) > 17 Then 

    StartingDateTime = Format(DateAdd("d", 3, StartingDateTime), "MM/DD/YY") & " 09:00 AM" 

ElseIf Format(StartingDateTime, "DDDD") = "Saturday" Then 

    StartingDateTime = Format(DateAdd("d", 2, StartingDateTime), "MM/DD/YY") & " 09:00 AM" 

ElseIf Format(StartingDateTime, "DDDD") = "Sunday" Then 

    StartingDateTime = Format(DateAdd("d", 1, StartingDateTime), "MM/DD/YY") & " 09:00 AM" 

End If

with this

Code: 
If Format(StartingDateTime, "DDDD") = "Friday" And Hour(StartingDateTime) > FormatDateTime(HourEnd, vbShortTime) Then

    StartingDateTime = Format(DateAdd("d", 3, StartingDateTime), "MM/DD/YY") & " " & FormatDateTime(HourStart, vbShortTime) & ""

ElseIf Format(StartingDateTime, "DDDD") = "Saturday" Then

    StartingDateTime = Format(DateAdd("d", 2, StartingDateTime), "MM/DD/YY") & " " & FormatDateTime(HourStart, vbShortTime) & ""

ElseIf Format(StartingDateTime, "DDDD") = "Sunday" Then

    StartingDateTime = Format(DateAdd("d", 1, StartingDateTime), "MM/DD/YY") & " " & FormatDateTime(HourStart, vbShortTime) & ""

End If

What this does is take in to account your start and end hour. It was previously hard coded so you may get some weird (negative) values if you were to change the start time to something like 8:00 AM (or the end time).

HTH,
CT
 
Upvote 0
Thanks CT! I tried adding your "enhancements", I ended up half way between what I had and what you suggested. Here is why:

Code: 
(StartingDateTime, "DDDD") = "Friday" And Hour(StartingDateTime) > FormatDateTime(HourEnd, vbShortTime) Then

    StartingDateTime = Format(DateAdd("d", 3, StartingDateTime), "MM/DD/YY") & " " & FormatDateTime(HourStart, vbShortTime) & ""

And in my query I have:
Response Time: nhw([ADATECREATED],[ADATECLOSED]," 8:00","20:00")

I get:

START END Response Time
1/30/04 7:50PM 2/2/04 8:34AM 0:44 (good)
2/2/04 8:11AM 2/2/04 10:17AM -22:-43 (not good, sb 2:06)
_____________-
When I do it this way, I get exactly what I want, which is to calculate 10 mins of elapsed time on Friday afternoon and then hold and continue counting from 8:00AM Monday:
Code: 
If Format(StartingDateTime, "DDDD") = "Friday" And Hour(StartingDateTime) > 20 Then

    StartingDateTime = Format(DateAdd("d", 3, StartingDateTime), "MM/DD/YY") & " " & FormatDateTime(HourStart, vbShortTime) & ""

In my query I still have
Response Time: nhw([ADATECREATED],[ADATECLOSED]," 8:00","20:00")

I get:

START END Response Time
1/30/04 7:50PM 2/2/04 8:34AM 0:44 (good)
2/2/04 8:11AM 2/2/04 10:17AM 2:06 (good)


Thanks again for all your assistance! :biggrin:
 
Upvote 0
One more question for you CT...

Is there a way to convert the hh:mm:ss to a numeric value?

I am needing to do some analysis on the output, but realized today, since it is seen as text and not numeric, this won't work.
I tried to multiply by 1 or to change the format of the column, but this isn't working either.

Do you follow what I am asking?
 
Upvote 0
I'm not sure what kind of numeric format you want. IE

206 as 2 hours, 6 minutes
126 as minutes

etc,

please provide an example of what you are after. What will you be using this value for?

CT
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Time Of Use Data Analysis (Electricity) / Retailer Comparison
Replies
3
Views
902
naigy
N
P
  • Solved
Calculate based on date sum of hours based on date
2
Replies
15
Views
698
psycoperl
P
R
  • Question
Calculating Man Hours Worked on Day Night Shift
Replies
0
Views
2K
richy89
R
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
995
AndyEd
A
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A

Forum statistics

Threads
1,221,704
Messages
6,161,390
Members
451,701
Latest member
ckrings

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top