Calculate working days but change working week
 

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

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