Hourly allocation based on Start, end date and allocation %

[sunny1102]

Hey There,
I need help with excel allocation formulas. I attached a sample spreadsheet. I will need two formula options. So, here is what I need:


For an employee, I will enter three parameters - Start Date, End Date, and allocations ( as a percentage). Based on these inputs, I want to allocate hours in the monthly columns
For Eg: in Row 1, I have Jon starting on Jan 12 and ending on Nov 30 at a 100% allocation, So, I will need the formula to allocate hours in the month columns based on these inputs. So, the two formulas I need are:
1. Allocate hours in monthly columns based on 160 hours per month option
2. Allocate hours in monthly columns based on actual working days in the respective month column (US working days)

I am unable to attach the file. I am brand new to this forum and not sure if it is the reason. But, your help here will be greatly appreciated.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Allocation %[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]1/1[/TD]
[TD]11/30[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]2/15[/TD]
[TD]9/30[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Laura[/TD]
[TD]7/15[/TD]
[TD]12/31[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Melanie[/TD]
[TD]6/6[/TD]
[TD]8/30[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, welcome to the board.

Please can you add to your table, details of what the results should be ?

 

[GR00007]

There's probably an easier way of doing this - This is utilizing a second sheet with each day of 2019 with a 0 for weekends and holidays and a 1 for workdays. The calculation does a SUMIFS summing workdays between the start/end dates within each month then multiplying with the allocation rate by 8 hours a day. Since the requirement was to tally only workdays the 160 hour month was not used.

Each month calculation was done separately for row 2, but then copying down worked fine:
January calculation:

=SUMIFS(Sheet2!$B:$B,Sheet2!$A:$A,">="&B2,Sheet2!$A:$A,"<="&C2,Sheet2!$A:$A,">=1/1/2019",Sheet2!$A:$A,"<=1/31/2019")*8*D2

February through December changed the hard coded begin/end to the appropriate month making sure the criteria columns remained B2, C2, D2. I left them without the $ so that they's copy down correctly.

Sheet1
[TABLE="class: grid, width: 1039"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Allocation %[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]11/30/2019[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD="align: right"]2/15/2019[/TD]
[TD="align: right"]9/30/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]184[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Laura[/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]114[/TD]
[/TR]
[TR]
[TD]Melanie[/TD]
[TD="align: right"]6/6/2019[/TD]
[TD="align: right"]8/30/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2
[TABLE="class: grid, width: 144"]
<tbody>[TR]
[TD]Date[/TD]
[TD]WorkDay?[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Rest of year[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/25/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12/26/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/27/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/28/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12/29/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12/30/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]