calculating difference between date/time fields

[drew]

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!

 

[bst]

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!

you can use the timevalue function.

dim differenceInTime as date
with sheets("SheetName")
differenceInTime = timevalue(.range("I3").value) - timevalue(.range
("G3").value)
end with
you would need to alter the code if the it is more than a day i think.
hth
bst

 

[Tom Ogilvy]

http://www.cpearson.com/Excel/DateTimeWS.htm#WorkHours

 

[Mike H]

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),TIME(8,0,0))

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

Mike

 

[drew]

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

 

[Mike H]

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
=G3>I3
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]

If negative numbers are an issue, simply putting "on time" for any that are
negative would be fine.

 

[Joel]

Thsi is a little complicated if you are considering every possible time for
both the start and end time. I recommend a UDF. See the comments in the
code below.

The cell with the formula may need to be set as text so excel doesn't
convert the hours to a number less than 24. If you have 37:00 excel may
change this to 12:00 (37 - 24 hours in a day) if the cell is left in general
format.



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 = 24 * FractDays
FractMinutes = Hours - Int(Hours)
Minutes = Round(60 * FractMinutes, 0)

WholeHours = Int(Hours) + (24 * Days)
WholeMinutes = Int(Minutes)

BusinessHours = _
WholeHours & ":" & Format(WholeMinutes, "#00")
End Function

 

[drew]

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

 

[Joel]

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

 

[Mike H]

Hi,

Format the formula cell with a custom format of

[hh]:mm

and you will get the correct answer in hours no matter what the time period.

Mike

 

[Mike H]

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

 

[drew]

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]

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

 

[Mike H]

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

 

[Mike H]

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

 

[Joel]

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

 

[drew]

Guys - Thank you both for this. This will help a lot!

 

[drew]

Joel - This doesn't seem to be taking weekends into account. For example I
have 7/11/2008 4:00 PM
7/14/2008 9:00 AM

It should give me 2:00, but it is giving me 18:00. Can you assist? Thanks

 

[drew]

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