Joel,

To make it work if the deal comes in early you need to swap the values so I
added these couple of lines at the start of your UDF.

If Endtime < StartTime Then
temp = StartTime
StartTime = Endtime
Endtime = temp
End If

Mike

"Joel" wrote:

I simplified my code a little bit but this UDF should give the correct answer.

Function BusinessHours(StartTime As Date, Endtime As Date)

Dim Days As Double
Dim DiffTime As Double
Dim FractDays As Double
Dim FractMinutes As Double
Dim Hours As Double
Dim Minutes As Double
Dim Time_15H As Double
Dim WholeHours As Double
Dim WholeMinutes As Double

'Move Start time to beginning of next working day
If Hour(StartTime) 17 Then
'then add one day and move time to 8:00 AM
StartTime = Int(StartTime) + 1
StartTime = StartTime + TimeSerial(8, 0, 0)
End If
If Hour(StartTime) < 8 Then
'move time to 8:00 AM
StartTime = Int(StartTime)
StartTime = StartTime + TimeSerial(8, 0, 0)
End If
'Move End Time to beginning of next working day
If Hour(Endtime) 17 Then
'then add one day and move time to 8:00 AM
EndTime = Int(EndTime) + 1
EndTime = EndTime + TimeSerial(8, 0, 0)
End If
If Hour(Endtime) < 8 Then
'move time to 8:00 AM
EndTime = Int(EndTime)
EndTime = EndTime + TimeSerial(8, 0, 0)
End If

DiffTime = Endtime - StartTime

'Now for every day subtract 15 hours (5:00 PM to 8:00 AM)
'days will be integer portion of DiffTime
Time_15H = 15 / 24

Days = Int(DiffTime)

DiffTime = DiffTime - (Days * Time_15H)
'Create a sdtring with hours and minutes

Days = Int(DiffTime)
FractDays = DiffTime - Days
Hours = hour(FractDays) + (24 * Days)
Minutes = minute(FractDays)

BusinessHours = _
Hours & ":" & Format(Minutes, "#00")
End Function


"drew" wrote:

Will this calculate over 24 hours? I tried to put like 6 days difference in
both cells and it came back with 9 hours.

"Mike H" wrote:

Hi,

There may be a formula to do it but how about this workaround. Use the formula

=(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0))

Then for the cell where the formula is apply a conditional format of
=G3I3
Apply a colour of (say) green

Now if your deal close early it will still show up as a positive number but
the cell colour will change to whatever colour you set.

Mike


"drew" wrote:

This looks great.. Is there any way to display the negative numbers?

"Mike H" wrote:

Try

=(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0))

This wouldn't work if a deal closed early so post back if that's an issue.

Mike

"drew" wrote:

I am trying to count the difference between two fields which are date/time to
show if we met our service level agreements. However, I don't want to count
non business hours.

Business Hours are 8-5.

For example

Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM

Cell I3 is actual closing date 6/23/2008 10:45 AM

I would expect to see (1:45)

Can anyone help? Thanks!