# Date and time calculations with exclusion period

#### jasonconlon

##### Board Regular
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
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
L

#### jasonconlon

##### Board Regular
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!

L

#### Legacy 98055

##### Guest
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

#### jasonconlon

##### Board Regular
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--

L

#### Legacy 98055

##### Guest
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

Replies
7
Views
1K
Replies
1
Views
1K
Replies
10
Views
533
Replies
4
Views
399
Replies
1
Views
337

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,845
Messages
5,766,751
Members
425,378
Latest member
kapoor2892

### 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.

### Which adblocker are you using?

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

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