Joel, do you know how I could not include Saturday and Sunday into this
function? Thanks

"Joel" wrote:

The problem was easy to fix. The problem occured because the endtime was
earlier in the day then the starttime. If you start at 4:00 PM and end at
9:00 PM the number of days is less than one, but you still have to subtract
15 hours from the difference in the two times. I was only subtracting 15
hours if there was more than one day.

Function BusinessHours(StartTime As Date, Endtime As Date)

Dim Days As Double
Dim DiffTime As Double
Dim FractDays As Double
Dim FractEndTime As Double
Dim FractStartTime As Double
Dim Hours As Double
Dim Minutes As Double
Dim Time_15H As Double

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

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

'If End time of day < Starttime time of day
'subtract an extra 15 hours
FractStartTime = StartTime - Int(StartTime)
FractEndTime = Endtime - Int(Endtime)
If FractEndTime = FractStartTime Then
DiffTime = DiffTime - (Days * Time_15H)
Else
DiffTime = DiffTime - ((Days + 1) * Time_15H)
End If
'Create a sdtring with hours and minutes

Days = Int(DiffTime)
FractDays = DiffTime - Days
'Hours = Hour(FractDays) + (24 * Days)
Hours = Hour(FractDays) + (24 * Days)
Minutes = Minute(FractDays)

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


"Mike H" wrote:

Joel,

Sorry, I meant to add that while it can be done with a formula your UDF
would be far more flexible and is well worth persevering with because I would
hate to have to change all the cell reference in that monoltithic thing I
created :)

Mike

"Mike H" wrote:

Joel,

I initially thought that the formula could be done with worksheet funcntion

It can my formula gives the correct results. I've just done a bit more
testing with these 2 values

due 1/1/2008 09:00
delivered 31/12/2007 16:00

Your UDF returns 17 hrs when I believe the correct answer is 2 Hrs according
to the OP request.

Mike

"Joel" wrote:

I added Mike's code to mine. You need to call the formula with two
paramaters like

=BusinessHours(A1,A2) , where A1 and A2 are in any date format. It
doesn't matter which date format because Excel really stores the date as a
number and just displays the number differently depending on the way you
format the cell.

I initially thought that the formula could be done with worksheet funcntion
but I quickly discovered it would be extremely complicated. UDF is easier to
debug and add comments so the code can be maintiained.

The easiest way of inserting the macro is the following.

1) Press Alt-F11
2) From VBA menu Insert - Module.
3) copy code from posting from the word "Function" to "End Function"
4) Paste into the VBA module window.

The function works exactly like any other worksheet function.

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

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

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

I am a little novice when it comes to VBA, but would it simply be
=businesshours(g3-i3) after pasting the formula? Would I paste your entire
wording? Thanks

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