week --> Month

I have weekly data in a sheet. Obviously, weeks can span across months e.g. week of 28 Jan 2008 is having Fri, Sat and Sun falling on Feb 1,2,3. What I want is to *accurately* convert that weekly data pro-rata basis into monthly data (1st of Month thru last calendar day of that month). I am able to do approximation but that not accurately..

Please see attached xls sheet (zipped)

Hi nougain,

I don't think I understand what you're trying to do here. Are you saying that you want the data for each week divided into how many days of that week fall into a particular month, then added to the remaining data for that month? For example, the week of 1-28 for Alice would be 5/7 * 1.00 or ~0.71 ?

In the xls file attached, the DATA table shows % allocation (in decimal form) for a person each week. So .50 would mean that that person is allocated 50% for that week. Definition of week is "Mon thru Sun" having standard 40 working hours, Sat/Sun are non-working days. So, a 0.50 allocation would mean that the person has been allocated for 20 hours in that week.

Based on this allocation I can find weekly allocated hours for each person. Now, what I want to do is simply translate this data so that I could know how many hours a person is allocated month wise.

Example... for Tom for Task2, he is allocated 80% for the week of Jan 28, that is for 32 hours. Now, week of Jan 28 has Fri, Sat, Sun falling in the month of Feb. Because one working day of the week is falling in Feb, I wanted to account just four days in the month of Jan, that is just 25.6 hrs (32*4/5).

Hope this makes question more clear. Thanks.

any suggestion/lead?