# Hourly allocation based on Start, end date and allocation %

#### sunny1102

##### New Member
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

##### Well-known Member
Hi, welcome to the board.

#### GR00007

##### Board Regular
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:
Code:
``=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>

1,082,131
Messages
5,363,337
Members
400,726
Latest member

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...