Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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!
  #2   Report Post  
Posted to microsoft.public.excel.programming
bst bst is offline
external usenet poster
 
Posts: 19
Default calculating difference between date/time fields

?B?ZHJldw==?= wrote in
:

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default calculating difference between date/time fields

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

--
Regards,
Tom Ogilvy


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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default calculating difference between date/time fields

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!

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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!



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default calculating difference between date/time fields

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!

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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

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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default calculating difference between date/time fields

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

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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!

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default calculating difference between date/time fields

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!



  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default calculating difference between date/time fields

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

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

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default calculating difference between date/time fields

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!

  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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.

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!

  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default calculating difference between date/time fields

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.

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!

  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default calculating difference between date/time fields

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.

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!



  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default calculating difference between date/time fields

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.

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!

  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default calculating difference between date/time fields

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.

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!

  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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

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

  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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

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

  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default calculating difference between date/time fields

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
difference of date/time only calculating workingdays/hours Susanne Excel Worksheet Functions 4 February 5th 09 01:42 PM
calculating difference in time tam25 Excel Discussion (Misc queries) 4 September 7th 07 09:26 AM
Daylight Saving Time (DST) and calculating time difference. Peter T Excel Programming 3 January 19th 07 10:51 AM
Daylight Saving Time (DST) and calculating time difference. Chip Pearson Excel Programming 1 January 17th 07 03:35 PM
Calculating Difference Between Start Date & Time And End Date & Ti Samwar Excel Discussion (Misc queries) 2 December 19th 05 12:42 PM


All times are GMT +1. The time now is 04:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"