Ugh..another time and date problem...HELP
 

Okay, I was able to determine the duration of a Help Desk trouble ticket by
subtracting the received date and time from the closed date and time. Now,
here's the problem. The Help Desk is only open for 10 hours therefore the 24
hour period isn't quite accurate. Also if a trouble ticket extends over the
weekend it also skews the statistics.

Do any "brains" out there have any suggestions? (My brain is fried!) :)

--Sandy

Sandy,

Take a look at the NETWORKDAYS function, part of the Analysis Toolpak add-in, to account foir the
weekends.

Also, once you can get days, and then multiply by 10, etc., etc. to adjust, to get hours.

If you post specific times and dates, with the expected results of a calculation, then we can help
you more.

HTH,
Bernie
MS Excel MVP


"Sandy" wrote in message
...
Okay, I was able to determine the duration of a Help Desk trouble ticket by
subtracting the received date and time from the closed date and time. Now,
here's the problem. The Help Desk is only open for 10 hours therefore the 24
hour period isn't quite accurate. Also if a trouble ticket extends over the
weekend it also skews the statistics.

Do any "brains" out there have any suggestions? (My brain is fried!) :)

--Sandy

It's a bit tricky but can be done.
It's based on the fact that Excel calculates dates and time from
running numbers but it looks like it's date and time. Anyhow...

Here is one of the formulas, but best is to view example file -
attached.
=IF(NETWORKDAYS(B8,C8,$E$2:$E$4)-10,(C8-(INT(C8)+$C$2))-(B8-(INT(B8)+$C$3))+(NETWORKDAYS(B8,C8,$E$2:$E$4)-2)*$C$4,C8-B8)

The formula calculates hours - used - and deductes weekends and -
customized - holidays. You can also change helpdesk open hours.


Hope it can give you some ideas
Ola Sandström


Example zip-file:
http://www.excelforum.com/attachment...tid=3647&stc=1


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3647 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390684

Bernie is an example of my current worksheet. The Titles are skewed, but
other than that you can get the idea. The results are figured on a 12 hour
clock and I was lucky to get the duration and then the days, hours, minutes.
Anything you can do to help would be most appreciated.

Closed Recvd Duration DAYS hr min
4/1/05 12:08 4/1/05 8:13 3:54 00 03:54
4/4/05 9:35 4/1/05 13:47 67:48 02 19:47
4/1/05 10:59 4/1/05 9:35 1:23 00 01:23
4/1/05 7:40 4/1/05 4:32 3:08 00 03:07
4/5/05 13:39 4/1/05 11:28 98:11 04 02:11
4/7/05 13:07 4/1/05 10:52 146:15 06 02:14
4/4/05 9:50 4/1/05 14:20 67:30 02 19:29


"Bernie Deitrick" wrote:

Sandy,

Take a look at the NETWORKDAYS function, part of the Analysis Toolpak add-in, to account foir the
weekends.

Also, once you can get days, and then multiply by 10, etc., etc. to adjust, to get hours.

If you post specific times and dates, with the expected results of a calculation, then we can help
you more.

HTH,
Bernie
MS Excel MVP


"Sandy" wrote in message
...
Okay, I was able to determine the duration of a Help Desk trouble ticket by
subtracting the received date and time from the closed date and time. Now,
here's the problem. The Help Desk is only open for 10 hours therefore the 24
hour period isn't quite accurate. Also if a trouble ticket extends over the
weekend it also skews the statistics.

Do any "brains" out there have any suggestions? (My brain is fried!) :)

--Sandy

Ola,

This was a wonderful solution and *almost* works. Most of the calculations
work fine with my data however a few of the dates end up being negative and
I'm not sure why. I was duly impressed with your formula and hate to ask for
more help, but would you mind looking at my worksheet to see if you can
determine the problem? I don't know how to attach a file to a message, but I
can send an example of the data he (Several of my Working Time fields end
up in the negative).

Case Opened Closed Working Time Time for

Customer

# 1 2005/06/01 13:19 2005/06/01 14:03 0:43:38 0:43:38
# 2 2005/06/01 09:26 2005/06/01 09:39 0:13:41 0:13:41
# 3 2005/06/01 12:15 2005/06/01 15:09 2:53:59 2:53:59
#4 2005/06/01 11:22 2005/06/01 12:13 0:51:24 0:51:24
2005/06/01 10:38 2005/06/01 14:59 4:20:56 4:20:56
2005/06/01 12:11 2005/06/01 12:46 0:34:30 0:34:30
2005/06/01 07:38 2005/06/01 08:20 0:42:15 0:42:15
2005/06/01 08:46 2005/06/01 09:27 0:41:31 0:41:31
2005/06/01 13:00 2005/06/01 15:13 2:12:45 2:12:45
2005/06/01 20:50 2005/06/02 09:02 ##### 12:11:56
2005/06/01 10:45 2005/06/01 10:58 0:12:47 0:12:47
2005/06/01 03:22 2005/06/01 08:41 5:19:39 5:19:39
2005/06/01 08:10 2005/06/01 10:29 2:18:51 2:18:51
2005/06/01 04:02 2005/06/01 07:50 3:47:50 3:47:50
2005/06/01 04:24 2005/06/01 08:56 4:32:21 4:32:21
2005/06/01 14:57 2005/06/01 17:04 2:07:14 2:07:14
2005/06/01 08:41 2005/06/01 10:13 1:31:30 1:31:30
2005/06/01 11:08 2005/06/01 12:32 1:24:23 1:24:23


You are a genius! Thanks again!


"olasa" wrote:


It's a bit tricky but can be done.
It's based on the fact that Excel calculates dates and time from
running numbers but it looks like it's date and time. Anyhow...

Here is one of the formulas, but best is to view example file -
attached.
=IF(NETWORKDAYS(B8,C8,$E$2:$E$4)-10,(C8-(INT(C8)+$C$2))-(B8-(INT(B8)+$C$3))+(NETWORKDAYS(B8,C8,$E$2:$E$4)-2)*$C$4,C8-B8)

The formula calculates hours - used - and deductes weekends and -
customized - holidays. You can also change helpdesk open hours.


Hope it can give you some ideas
Ola Sandström


Example zip-file:
http://www.excelforum.com/attachment...tid=3647&stc=1


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3647 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390684