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