Work Distribution over Weeks

**Jdnethaji** · 01-26-2013, 11:45 AM

Gentlemens,
I am new to this forum and hoping for your help for my below problem.

start date:20-12-2012
End date: 18-3-2013
Job to be achieved during this period (except friday work) is 700

looking for, the above job to be distributed in respective weeks in between the above periods.

Thanks for your valuable time to look in this problem.

JD

**benishiryo** · 01-26-2013, 09:07 PM

hi JD, welcome to the forum. say Start date in A2 & End date in B2, then this will give you the days without Fridays:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))<7))

to obtain the Jobs per day is simply:
=700/SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))<7))

you could even have a list of holidays, say in F2:F6 & this will calculate the number of holidays within your date range:
=SUMPRODUCT(($F$2:$F$6>=A2)*($F$2:$F$6<=B2))

so if you need to, you can combine the Jobs/(workdays - holidays)

if you have Excel 2010, there is a new function NETWORKDAYS.INTL where you can choose workdays that is Sunday to Thursday.

**bebo021999** · 01-26-2013, 09:43 PM

Tips of counting the weekdays within the range of date, to avoid CSF from array formular:
Numbers of Monday: =INT((B2-B1-WEEKDAY(B2-0,2)+8)/7)
Numbers of Tuesday: =INT((B2-B1-WEEKDAY(B2-1,2)+8)/7)
...
Numbers of Friday: =INT((B2-B1-WEEKDAY(B2-4,2)+8)/7)

**Jdnethaji** · 01-27-2013, 05:20 AM

Thanks for your time and advise Mr.Benishiryo,

please find attached file showing the scenario, i would like to show the values in the respective weeks, also the values to be highlighted using conditional formats.

thanks for putting your thoughts again.

Regards,
JD.

**Jdnethaji** · 01-27-2013, 05:25 AM

Thanks Mr. Bebo021999,

Your is good one to find the specific days between the periods, but similar to this i am using with little change =INT((WEEKDAY(C220-6)+D220-C220)/7); where C220 is a start date and D220 is a end date.

Thanks & Regards,
JD.