Calculate Net Work Days for 4 day weeks
The networkday function calculates days based on a 5 day work week. I have
staff who work 4 day weeks and would like to calculate net work days based on the 4 day week. Any ideas of how to do this. -- END |
Calculate Net Work Days for 4 day weeks
Hi,
Which is the weekday they don't work? if it's a Friday try this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<5),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0)))) Where a1 and B1 are the start end days and C1 - c8 are any holidays you wabt to exclude. Mike "Darin" wrote: The networkday function calculates days based on a 5 day work week. I have staff who work 4 day weeks and would like to calculate net work days based on the 4 day week. Any ideas of how to do this. -- END |
Calculate Net Work Days for 4 day weeks
Hi,
Friday is the day they don't work, but when I try the coding you suggested I am not getting a correct result. Admitidly I have not familiar with some of the functions you suggested. I specified a begin date of 7/5/2009 and an end date of 7/18/2009 and no holiday, and expected the result to be 8, but am getting 22. This is the formula I tried ... did I get something wrong? =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<5)--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1,0)))) Thanks Darin -- END "Mike H" wrote: Hi, Which is the weekday they don't work? if it's a Friday try this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<5),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0)))) Where a1 and B1 are the start end days and C1 - c8 are any holidays you wabt to exclude. Mike "Darin" wrote: The networkday function calculates days based on a 5 day work week. I have staff who work 4 day weeks and would like to calculate net work days based on the 4 day week. Any ideas of how to do this. -- END |
Calculate Net Work Days for 4 day weeks
Hello,
A solution without holidays: =A2-A1+1-INT((A2-MOD(A2-1,7)-A1+7)/7)-INT((A2-MOD(A2-6,7)-A1+7)/7)-INT ((A2-MOD(A2-7,7)-A1+7)/7) A1: Start Date A2: End Date Regards, Bernd |
Calculate Net Work Days for 4 day weeks
That works ... Thanks!
-- END "Bernd P" wrote: Hello, A solution without holidays: =A2-A1+1-INT((A2-MOD(A2-1,7)-A1+7)/7)-INT((A2-MOD(A2-6,7)-A1+7)/7)-INT ((A2-MOD(A2-7,7)-A1+7)/7) A1: Start Date A2: End Date Regards, Bernd |
All times are GMT +1. The time now is 04:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com