excelexcel-formulaexcel-2019

Resource assignment between specific period

I'm calculating resource allocation in a month. Each row represent person's allocation.

I'm using 2019 excel version so XLOOKUP cannot be used.

Calculations: Header has Allocation Start Date and End Date, followed by different months (say M1, M2, M3 and so on). Here M1 is 01-Jul-24, M2 is 01-Aug-24, M3 is 01-Sep-24, etc.

Allocation calculations: Number of allocations days (work days) Divided by Number of Total Work Days in that month. For 1st row since the allocation is only for 5 days in M3 (Sep-24) then Allocation would be 5 Divide by 21 = 0.24

Start End 07/01/24 08/01/24 09/01/24 10/01/24 11/01/24 12/01/24
09/02/24 09/06/24 0.00 0.00 0.24 0.00 0.00 0.00
07/01/24 10/15/24 1.00 1.00 1.00 0.48 0.00 0.00
11/01/24 12/21/24 0.00 0.00 0.00 0.00 1.00 0.68

Any help is appreciated

Solution

  • I added a 01/01/2025 in column I.

    Formula in C2 is this:

    =IF(OR($A2>D$1-1,$B2<C$1),0,NETWORKDAYS.INTL(MAX($A2,C$1),MIN($B2,D$1-1),1,)/NETWORKDAYS.INTL(C$1,D$1-1,1,))

    If startDate > MonthEnd, or endDate < MonthStart, allocation = 0;

    otherwise, we use MAX(startDate, MonthStart) as "effective start date", MIN(endDate, MonthEnd) as "effective end date".

    You can fill it to the right, then fill it down. enter image description here