Calculate working days but change working week
=(NETWORKDAYS(J36+I36,K36+I36)-2)*10/24
+((WEEKDAY(J36+I36,2)<6)*(MAX(0,TIME(17,0,0)-MOD(J36+I36,1))))-((WEEKDAY(J36+I36,2)<6)*(MAX(0,TIME(7,0,0)-MOD(J36+I36,1))))
+((WEEKDAY(K36-I36,2)<6)*(MAX(0,MOD(K36-I36,1)-TIME(7,0,0))))-((WEEKDAY(K36-I36,2)<6)*(MAX(0,MOD(K36-I36,1)-TIME(17,0,0))))
where I36 holds
0 - Mon-Fri
1 - Sun-Thu
2 - Sat-Wed
--
__________________________________
HTH
Bob
"SamB" wrote in message
...
Hi
I have different timestamps that I need to calculate the time between them
however I need to exclude evenings and weekends.
I'm using the standard NETWORKDAY function which works great for those
countries who work Monday Friday.
However I need to also calculate the same for those countries working Sun
Thurs and Sat Wed
The formula that I have is:
=(NETWORKDAYS(J36,K36)-2)*10/24+((WEEKDAY(J36,2)<6)*(MAX(0,TIME(17,0,0)-MOD(J36,1))))-((WEEKDAY(J36,2)<6)*(MAX(0,TIME(7,0,0)-MOD(J36,1))))+((WEEKDAY(K36,2)<6)*(MAX(0,MOD(K36,1 )-TIME(7,0,0))))-((WEEKDAY(K36,2)<6)*(MAX(0,MOD(K36,1)-TIME(17,0,0))))
i.e.:
J36 = 12/08/2008 09:00
K36 = 15/08/2008 10:00
TAT = 31:00
This stops the clock at 17.00 and starts it at 7.00 which I still need, I
just need to say that the working week is different.
i.e. if the 13th & 14th are a weekend the calculation would be 11.00 hours
but I can not figure out how to change the formula.
I've looked at this too long now, any ideas?
Thanks
Sam
|