Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To work out how many days, weeks, months to I retire on 20 Sep 201 | Excel Worksheet Functions | |||
How do I calculate a difference in work days? | Excel Worksheet Functions | |||
Calculate Number of Months Weeks and Days Between Two Dates | Excel Worksheet Functions | |||
How to calculate in weeks and days? | Excel Discussion (Misc queries) | |||
How do I calculate work days? | Excel Discussion (Misc queries) |