Hourly allocation based on Start, end date and allocation %

sunny1102

New Member
Joined
Jan 6, 2019
Messages
1
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.


NameStart DateEnd DateAllocation %JanFebMarAprMayJunJulAugSepOctNovDec
Tony1/111/3050
Jon2/159/30100
Laura7/1512/3175
Melanie6/68/30100

<tbody>
</tbody>
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

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

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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
NameStart DateEnd DateAllocation %JanFebMarAprMayJunJulAugSepOctNovDec
Tony1/1/201911/30/20190.584808088928088888092760
Jon2/15/20199/30/20191080160176184160176176160000
Laura7/15/201912/31/20190.7500000078132120138114114
Melanie6/6/20198/30/20191000001361761760000

<tbody>
</tbody>
Sheet2
DateWorkDay?
1/1/20190
1/2/20191
1/3/20191
1/4/20191
1/5/20190
1/6/20190
1/7/20191
Rest of year
12/25/20190
12/26/20191
12/27/20191
12/28/20190
12/29/20190
12/30/20191
12/31/20190

<tbody>
</tbody>
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top