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

[Sandy]

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

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[olasa]

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

[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 here: (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)-1>0,(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
>
>