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.

Name

Start Date

End Date

Allocation %

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Tony

1/1

11/30

50

Jon

2/15

9/30

100

Laura

7/15

12/31

75

Melanie

6/6

8/30

100

<tbody>
</tbody>

 

[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

Name	Start Date	End Date	Allocation %	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Tony	1/1/2019	11/30/2019	0.5	84	80	80	88	92	80	88	88	80	92	76	0
Jon	2/15/2019	9/30/2019	1	0	80	160	176	184	160	176	176	160	0	0	0
Laura	7/15/2019	12/31/2019	0.75	0	0	0	0	0	0	78	132	120	138	114	114
Melanie	6/6/2019	8/30/2019	1	0	0	0	0	0	136	176	176	0	0	0	0

<tbody>
</tbody>

Sheet2

Date	WorkDay?
1/1/2019	0
1/2/2019	1
1/3/2019	1
1/4/2019	1
1/5/2019	0
1/6/2019	0
1/7/2019	1
Rest of year
12/25/2019	0
12/26/2019	1
12/27/2019	1
12/28/2019	0
12/29/2019	0
12/30/2019	1
12/31/2019	0

<tbody>
</tbody>