Date and time calculations with exclusion period

jasonconlon

Board Regular
Joined
Mar 14, 2002
Messages
80
I've got a tricky one, and I can't see any way around it except to draw up some lengthy, convoluted formulae. And so I was wondering if anyone else might have a better idea.

I'm trying to track email response times, based on the time that the email was received (offered) and the time that the email was responded to (answered). I can easily work out the TOTAL answer delay just by subtracting the answer time from the offered time. The tricky part is that I would like to automatically calculate only the time that has transpired during working hours --

For instance, if the e-mail was received on a Sunday but was replied to at 9:30am Monday, then the Work Hours Answer Delay should show just 30 minutes. Or if the e-mail is received at 5:30pm on Monday but isn't answered until 9:30am on Tuesday, then the Work Hours Answer Delay should show just 60 minutes.

How would you tackle this?
Book2
ABCDEFGHIJKLM
1IndividualEmailReport
2OfferedNoAnswerAnsweredWorkHoursStartFinish
3DayDateTimeNoAnswerDayDateTimeTotalWorkHrsWorkDaysMondayFriday
4OfferedOfferedOfferedRequiredAnsweredAnsweredAnsweredAnsDelayAnsDelayWorkDayHours09:00AM06:00PM
5Monday12-Aug11:58AMMonday12-Aug02:51PM2:53:00
6Monday12-Aug01:47PMMonday12-Aug03:09PM1:22:00
7Tuesday13-Aug09:53AM 
8Sunday25-Aug09:53PMMonday26-Aug11:47AM13:54:00
9Monday26-Aug09:15AMMonday26-Aug11:57AM2:42:00
10Monday26-Aug03:36PMMonday26-Aug03:57PM0:21:00
11Wednesday28-Aug11:15AM 
Sheet1
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Upvote 0
Yes, those answers are perfectly correct!

(I can see you've created your own formula, but how did you do that?)

The ideal output format would be [h]:mm:ss (even though SS will always be "00").

You're a genius!
 
Upvote 0
You may delete the genius part after you try this user defined function. It's not very good and it may or may not work. Then again it may work fine for you. I'm gonna play around with it and will mail you the update.
I have seen solutions to these types of question using native functions on this board. Beyond me. Paste this function into a standard module. Use it as you would any other Excel function.

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

Application.Volatile

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
If TotalNetHoursWorked > 1440 Then
nhw = Int(TotalNetHoursWorked / 60) & ":" & TotalNetHoursWorked Mod 60 & ":00"
Else: nhw = 0.000694444 * TotalNetHoursWorked
End If
End Function

</pre>
Tom
 
Upvote 0
I've not worked with a user-defined formula before. Once I create 'nhw' will that be saved with the spreadsheet so that others can use it or will it only be available on my PC?

Thanks again for all your work with this, Tom. I genuinely appreciate it.

--Jason--
 
Upvote 0
Yes Jason.
It will stay with the spreadsheet and will be available as all other functions. To place the code in a standard module, do the following:

Press ALT, F11
This will bring up the VBE (Visual Basic Environment)
From the toolbar up top choose, Insert, Module.
This will automatically add a standard module to your project which will be saved right along with your workbook.
Paste the code in as is.

For help on the arguments of this function, type =nhw in your formula bar and then click the "=" to the left.

Check this thread later if you want. I am going to search for a native solution as well.

Tom

Found it.
See Dave Patton's post on this thread.
http://www.mrexcel.com/board/viewtopic.php?topic=19206&forum=2
This message was edited by TsTom on 2002-08-28 23:45
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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