Date and time calculations with exclusion period

[jasonconlon]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	IndividualEmailReport
2	Offered			NoAnswer	Answered						WorkHours	Start	Finish
3	Day	Date	Time	NoAnswer	Day	Date	Time	Total	WorkHrs		WorkDays	Monday	Friday
4	Offered	Offered	Offered	Required	Answered	Answered	Answered	AnsDelay	AnsDelay		WorkDayHours	09:00AM	06:00PM
5	Monday	12-Aug	11:58AM		Monday	12-Aug	02:51PM	2:53:00
6	Monday	12-Aug	01:47PM		Monday	12-Aug	03:09PM	1:22:00
7	Tuesday	13-Aug	09:53AM
8	Sunday	25-Aug	09:53PM		Monday	26-Aug	11:47AM	13:54:00
9	Monday	26-Aug	09:15AM		Monday	26-Aug	11:57AM	2:42:00
10	Monday	26-Aug	03:36PM		Monday	26-Aug	03:57PM	0:21:00
11	Wednesday	28-Aug	11:15AM
Sheet1

 

[Legacy 98055]

Hi Jason.
Are these examples correct?

TomsFunctions.xls
	A	B	C	D	E	F	G	H
19	Monday	12-Aug	11:58AM	Monday	12-Aug	2:51PM		2hrs.-53min.
20	Sunday	25-Aug	9:53PM	Monday	26-Aug	11:47AM		2hrs.-47min.
21	Friday	9-Aug	6:00PM	Monday	12-Aug	10:01AM		1hrs.-1min.
22	Friday	9-Aug	5:30PM	Wednesday	14-Aug	5:30PM		27hrs.-0min.
Sheet1

Do the values returned by the function need to be of a certain format? In the above, column H, they are returned as a string.
Tom
This message was edited by TsTom on 2002-08-28 22:51

 

[jasonconlon]

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!

 

[Legacy 98055]

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]

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

 

[Legacy 98055]

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