Hi,
In your case:
OpStart = 8:00
OpEnd = 17:00
Holidays: A range containing the holidays
Total worked hours (between OpStart and OpEnd) with a checkin at A1 and a
checkout at A2 is:
=IF(A1A2,0,NETWORKDAYS(A1,A2,Holidays)*(OpEnd-OpStart)
-IF(NETWORKDAYS(A1,A1,Holidays),MAX(0,MIN(OpEnd,MOD (A1,1))-OpStart),0)
-IF(NETWORKDAYS(A2,A2,Holidays),MAX(0,OpEnd-MAX(MOD(A2,1),OpStart)),0))
Format as you see fit, perhaps
[hh]:mm
Regards,
Daniel M.
"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
|