Formula to allocate no. of days to months given start & end dates

[paulma1960]

Hi all, I'm attempting to apportion a given number of man-days (of labour) to relevant months, for each labour division, given the start and end dates for each labour role. I'm not sure if I need to consider workdays (ie, exclude weekends).

In the uploaded worksheet, you can see I've calculated the Qty of Units/Labour Division, and I'm looking for a formula to fill the rest of the table.

As an example, for Security, 17 days are being allocated with a start date of 19/1/22 and end date of 4/11/22. I need 17 days to be allocated (to any decimal) over each month, pro-rata for January and November.

Any suggestions are most appreciated. TIA.

[AliGW]

Your sample file should contain at least SOME expected results, please, not an empty grid leaving your helpers to have to guess at what you want to see. Even if you feel you have described this, it may not be completely clear to others.

[paulma1960]

OK, I managed to work it out myself, and I've uploaded the file for reference for anyone who may be able to use it in the future.

Here's an explanation of what's going on:

• I have a list of job roles (starting in cell A29), the number of expected man-days required to be costed for the job (cell B29), and the start and end dates of the job
• I need to apportion the allocated man-days across one or more months. The idea is that as more projects come in, to see if there are enough people employed to cover the different roles.
• The table starting at cell H28 would have been sufficient, but there are one or more rows for the same role, so I need to aggregate them, which I've achieved in the table at cell A5.

Maybe my formulas could be improved, I'm open to possibilities. I could also refine the calculations by using NETWORKDAYS, but for now, this is working fine for me.

[paulma1960]

In the first table in the attached file, from the raw data in yellow, I have allocated per Labour Division, the number of days per month. This works fine.

Sometimes, the Start and End Dates per Labour Division are not given, and I have only the Project Start and End Dates (cells Y30:Z30), and I need to use these.

I've tried adapting my formulas in the first table to accept fixed dates, but SUMPRODUCT doesn't like this and I'm getting a #VALUE! error (cell C27). So the second table needs some work.

First, I'd like to get this second table working with the Default Project Start and End Dates, and ideally, have just the one table where it uses the dates in the yellow table, and if not entered, uses the Project dates. It seems my issue is around how to handle SUMPRODUCT with non-array (constant) values.

Any assistance is appreciated. TIA, Paul

[bsalv]

with VBA ?

[paulma1960]

Thanks, bsalv, but no, I need a formula solution, though I have no problem with VBA.

[bsalv]

ok, success

[paulma1960]

ok, success

I'm not sure what you mean. Did you mean to upload a Workbook?

[bsalv]

no, i meant succes with formulas.
Are there no known start or enddates at all and so all of them have to use the known start+end project date or is it even worse, a mixed form, some have a start+end, others not ?
In this last case ????
With VBA, you can use a mixed form.

[paulma1960]

no, i meant succes with formulas.
Are there no known start or enddates at all and so all of them have to use the known start+end project date or is it even worse, a mixed form, some have a start+end, others not ?
In this last case ????
With VBA, you can use a mixed form.

I think something's lost in translation. I don't understand your replies at all. I've found my own solution. In the attached file, I use the dates in the yellow table, per line. In the second table, I use the project start and end dates, which you can see below the yellow table. And in the third table, the data uses the dates in the yellow table if they're there, otherwise it will use the project dates. I hope you can follow this.

Thanks for your time, bsalv, though it was a formula solution I was looking for. And I don't like using Application.Volatile when I do use VBA.

[bsalv]

Oké, no hard feelings.
Interesting formulas.

[paulma1960]

Interesting formulas.

Basically, in the first table, the logic is, if certain conditions are met, get the number of labour days for each role, and apportion them according to the number of days in each month the labour occurs, pro-rata. For example, in the first line, Bids has 7.5 days allocated between 4-Aug and 6-Sep, which has a duration of 34 days. So there's 28 days allocated to Aug (28/34 x 7.5 = 6.88) and 6 days in Sep (6/34 x 7.5 = 0.62). I'm not considering NETWORKDAYS as the calculations require estimates only (projects never go exactly to plan), and weekends may be worked.