Hi Luke,

I think this is close but...

For example I have one opened Jan 24/09 00:33:43 which is a Sat. Closed
Jan 26/09 11:21:11, so the time should be 03:21:11 but I got 00:00:00.

Another is Open Jan 26/09 at 08:18:31 and closed at Jan 26/09 at 13:53:27,
so the time should be 05:34:56 but I ended up with ##### (a negative number)

Another is open June 24/09 at 16:59:09 and closed June 25/09 at 09:33:22
the time should be 01:34:13 and I got 00:00:51

Another is open at April 26/09 at 15:15:20 and close at May 08/09 at
11:22:36 which should be 203:07:16 (after taken out the weekends and the
April 10 Good Friday holiday) and I got 199:04:40

Any help would be greatly appreciated.

Thanks





"Luke M" wrote:

Note also that you'll want to format the cell with a custom format of:
[hh]:mm:ss

in order for all the hours to be displayed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

I think this will work:

=IF(M2="","",(NETWORKDAYS(F2,M2,C5:C20)-1)*9/24-IF(AND(ISNA(MATCH(F2,C5:C20,0)),WEEKDAY(F2,2)<6),M OD(F2,1)-TIME(8,0,0),0)+IF(AND(ISNA(MATCH(INT(M2),C5:C20)), WEEKDAY(M2,2)<6),MOD(M2,1)-TIME(8,0,0),0))

formula counts number of workdays, crediting 9 hrs per workday. Subtract any
hours not worked on first day if it was a workday, and add any hours on last
day if it was a workday.

The 2 MATCH functions are there to help you check for holidays. In this
formula, holidays are in range C5:C20.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"catts22" wrote:

Hi

Here is the formula I am using:

=IF(M2="","",(NETWORKDAYS(F2,M2))+TIME(17,0,0)-F2+M2-TIME(8,0,0))

What I need is the hh:mm:ss between two date/times not including any time on
Sat and Sun.

For example F2 has my start date and time which is Nov 1/2008 at 15:30:41
(this is a Saturday). M2 has the end time which is Monday Nov 3/2008 at
15:47:39. The result I'm getting is 09:16:58 (this is the 1:29:19 on the
Saturday between 15:30:41 and 17:00:00 and the 07:47:39 on the Monday between
8:00:00 and 15:47:39)

What I want is just the 07:47:39 between the 8:00:00 and 15:47:39 on the
Monday, as although the time started on the Saturday I don't want to count
the time on the Saturday because it is the weekend. (Essentially someone was
working on the Saturday opening up repair requests, but the repair people
work Mon to Fri between the hours of 8am and 5pm, so I dont want to penalize
them because someone else was working on a Saturday).

Over and above this if possilbe I need to take out holidays (eg. Labourday,
Dec 25 & 26, Canada Day-July 1)

Thanks for the help.