Translate ~Number of days in a week to number of days across months

Hi,

I am putting together a sheet where we are looking to get users to estimate the number of days to do a task for each week, i.e. W/c 17/11 = 5-days, 24/11 - 5 days, 1/12 - 5 days.

And then I need to provide an output of this as days per months, i.e. November - 10; December - 5.

Of course the start and end of weeks don't tie up witht the start and end of months so I have to be a little clever. I have started with an idea of using an intermediary sheet to list all of the days individually and then do a countif against the dates to pull back the number of days in each month.

But this is going to be unwieldy and also the number of columns in a sheet is going to limit the amount of time that users can enter the effort for, i.e. 254 columns = 254 days = 36-weeks.

Is there a way to use an array formula or something similar to do this calculation?

Thanks,

Martin

Hi,

You don't need anything as complicated as that. Provided you use date values, formatted however you want, you'll always be able to perform date arithmetic, probably with a SUMIF() function or some other variations. But you do need to define your requirement. For instance if someone projects 5 days for the w/c 29th December, does that count in the December total or January? Only you can decide that.

HTH

Thanks for the suggestions so far. I agree that I can just use some simple arithmetic to allocate the days all to the month that the week starts in - but as it ends up driving financial forecasting I don't think that this wil be sufficient.

I need to split the days across the months so there is an accurate split on the assumption that each week will 5-working days and if not then the days will split pro-rata across the month, i.e. if someone enters 3-days then there would be 0.6 days allocated to each day of that week.

Does anyone have any other ideas? Otherwise it does look like the best option will be to use the intermediate page.

Thanks,

Hi Martin,

I came up with the following, but have just realised that your working week is 5 days. The following is based on 7, but you can no doubt modify it - I haven't time just at the moment.

I created a table of months with 1-12 in the first column and the days in each month in the second column, and named this table "ME_Table".

Then with the Monday week start days starting in A6 going down, e.g. 24/11, 1/12, 8/12 etc. and the days for that week in B6....the following formula in C6 apportions the days when a week straddles a month boundary.

Please Login or Register  to view this content.

As I say it perhaps wants tweaking to reflect 5 day working week. Then presumably you'll just have a second table of month names with a SUMIF() to add the week values in each month.

HTH