S
SamB
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
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