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!

=?Utf-8?B?ZHJldw==?= <(E-Mail Removed)> wrote in
news:6C7996C1-6A7C-402D-A584-(E-Mail Removed):

> 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

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!

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

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

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" 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),TIME(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!

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

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!

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
> =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" 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),TIME(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!

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
> > =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" 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),TIME(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!