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.

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.

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.

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.

Apologies, I forgot the last arguement in the 2nd Match function. Should be:

=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,0) ),WEEKDAY(M2,2)<6),MOD(M2,1)-TIME(8,0,0),0))


(that little ",0" makes a big difference!)
--
Best Regards,

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


"catts22" wrote:

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.

Hi Luke,

This worked perfectly; thanks so much for your help.

I really appreciate it :)



"Luke M" wrote:

Apologies, I forgot the last arguement in the 2nd Match function. Should be:

=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,0) ),WEEKDAY(M2,2)<6),MOD(M2,1)-TIME(8,0,0),0))


(that little ",0" makes a big difference!)
--
Best Regards,

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


"catts22" wrote:

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.

Hi Luke,

Maybe I need a bit more help :(

So I understand... I think the MOD functions in the formula need a bit more
work? I need the difference between the start and end date starting at 8am
in the morning to 5pm (17:00:00). I'm not certain how the *9/24 in the
Networkday part of the forulat affects this.

The reason I ask is as I am looking for time between the 8am to 5pm time
period between Mon to Fri; so for example

Start Date End Date Result
Should be
Jun/02/09 23:13:49 Jun/03/09 10:20:56 #### (neg # 02:20:56
Mar/30/09 19:02:27 Mar/31/09 10:13:09 00:10:42 02:13:09

Because in the ticket was opened on a weekday, but after 17:00:00 the clock
needs to start at 8am the following morning.

Again, thanks so much.





IF(M753="","",(NETWORKDAYS(F753,M753,D$5:D$20)-1)*9/24-IF(AND(ISNA(MATCH(F753,D$5:D$20,0)),WEEKDAY(F753,2 )<6),MOD(F753,1)-TIME(8,0,0),0)+IF(AND(ISNA(MATCH(INT(M753),D$5:D$2 0,0)),WEEKDAY(M753,2)<6),MOD(M753,1)-TIME(8,0,0),0))

"Luke M" wrote:

Apologies, I forgot the last arguement in the 2nd Match function. Should be:

=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,0) ),WEEKDAY(M2,2)<6),MOD(M2,1)-TIME(8,0,0),0))


(that little ",0" makes a big difference!)
--
Best Regards,

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


"catts22" wrote:

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.