You're right, I made the assumption (wrongly) that during the week times
would only be during work hours. Needed to add some MIN/MAX checks.
new formula:
=IF(M2="","",(NETWORKDAYS(F2,M2,D5
20)-1)*9/24
-IF(AND(ISNA(MATCH(F2,D5
20,0)),WEEKDAY(F2,2)<6),MIN(TIME(17,0,0),MAX(TIME(8,0,0),MOD(F2,1)))-TIME(8,0,0),0)
+IF(AND(ISNA(MATCH(INT(M2),D5
20,0)),WEEKDAY(M2,2)<6),MIN(TIME(17,0,0),MAX(TIME(8,0,0),MOD(M2,1)))-TIME(8,0,0),0))
To explain:
The (NETWORKDAYS()-1)*9/24 part is to assume normal 9 hrs for full days of
work. (note that networkdays counts a partial day as 1, thus the need for -1).
9/24 is the fraction of work hours in a normal day.
We then subtract any hours not worked between 8 am and 5 pm, provided that
the start day was not a weekend or holiday (in which case it won't matter,
and we can safely assume clock will start counts at 8 am on some other day).
We then add any extra partial hours (remeber when we subtracted 1 during the
NETWORKDAYS?) from the end day, taking only the time worked between 8 am and
5 pm. This is also checked to make sure its not a weekend or holiday, in
which case end time is 5 pm on some other day that doesn't matter.
The MOD formula is used to extract the decimal from a divdend. In XL,
times/dates are stored as numbers, with time being the decimal part. FUnction
basically says "Take the number, divide by 1 (giving the same number) and
return the decimal portion".
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
catts22 said:
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
$20)-1)*9/24-IF(AND(ISNA(MATCH(F753,D$5
$20,0)),WEEKDAY(F753,2)<6),MOD(F753,1)-TIME(8,0,0),0)+IF(AND(ISNA(MATCH(INT(M753),D$5
$20,0)),WEEKDAY(M753,2)<6),MOD(M753,1)-TIME(8,0,0),0))
Luke M said:
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),MOD(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 said:
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
:
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!*
:
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),MOD(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!*
:
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.
