Revision
=MAX(0,(NETWORKDAYS(D1,E1,holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(D1),holid
ays,0)),0,MAX(0,TIME(17,0,0)-MOD(D1,1)))+IF(ISNUMBER(MATCH(INT(E1),holidays,
0)),0,MAX(0,MOD(E1,1)-TIME(8,0,0)))-MAX(0,TIME(8,0,0)-MOD(D1,1))-MAX(0,MOD(E
1,1)-TIME(17,0,0))
--
HTH
Bob Phillips
"Nortos" wrote in message
...
That nearly worked, but on testing I'm getting a bit of extra time? see
example below
Cell D1 04/01/2005 06:40
Cell E1 05/01/2005 07:40
Calculation:
=MAX(0,(NETWORKDAYS(D1,E1,Holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(D1),Holid
ays,0)),0,MAX(0,TIME(17,0,0)-MOD(D1,1)))+IF(ISNUMBER(MATCH(INT(E1),Holidays,
0)),0,MAX(0,MOD(E1,1)-TIME(8,0,0)))
Result: 10:19:34 Should be 9:00:00 I think?
I'm most likely missing somthing very silly, any ideas?
Really appreciate your help.
Steve.
"Bob Phillips" wrote:
Ho Nortos,
It is a bit more complicated than that. You also have to cater for
- the start date/time being after 17:00
- the end date/time being before 08:00
- holidays
- the start and/or end date being a holiday
This should do it, assuming that you have a name called holidays with
the
public holidays defined
=MAX(0,(NETWORKDAYS(A1,C1,holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(A1),holid
ays,0)),0,MAX(0,TIME(17,0,0)-MOD(A1,1)))+IF(ISNUMBER(MATCH(INT(C1),holidays,
0)),0,MAX(0,MOD(C1,1)-TIME(8,0,0)))
--
HTH
Bob Phillips
"Nortos" wrote in message
...
I would like to be able to caculate working time when the cell format
is
4/1/2005 6:40:26 AM (not seperated into date and time)
Cell A1 dd/mm/yyyy h:mm:ss (First Day)
Cell A2 dd/mm/yyyy h:mm:ss (Last day)
Working hours would be 8:00 to 17:00
Public holidays need to be taken out too
The result should be working days hours and minutes or just hours and
minutes, so that Saturday, Sunday, public holidays & between the hours
of
17:00 to 8:00 to the next working day would not count.
I've tried using:
=((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0)
But I think this will only work if the date and time are in seperate
cells.
Can any one help
|