How to auto allocate cost center percentages distribution into a time sheet

[Sophoslat]

I need help creating an excel time sheet template file to calculate the weekly allocation of worked hours for each employee. To start, every employee works a fixed amount of 8 hours per day (5 hours in the morning and 3 hours in the afternoon). There are 3 fixed cost centers that cover the salary and it is calculated as follows:
Cost center A, covers 20% per week
Cost center B, covers 35% per week
Cost center C, covers 45% per week

For a total of 40 hours per week, Cost center A, is 8 hours per week in total, Cost center B, is 14 hours per week, Cost center C, is 18 hours per week. If an employee takes time off, there will be a different cost center for time off and all the fixed cost centers will need to be adjusted/allocated evenly by the number of hours worked by the employee based on their percentages.

I'd like to be able to create an automated time sheet, that will only need the input of hours worked per employee; then, the spreadsheet will populate the number of hours worked per day in the AM and the PM for each cost center. for example, if employee X works 40 hours the schedule should be:
Monday AM (5 hours to Cost Center A)
Monday PM (3 Hours to Cost Center A)

Tuesday AM (5 hours to Cost Center B)
Tuesday M (3 Hours to Cost Center B)

Wednesday AM (5 hours to Cost Center C)
Wednesday PM (3 Hours to Cost Center B)

Thursday AM (5 hours to Cost Center C)
Thursday PM (3 Hours to Cost Center B)

Friday AM (5 hours to Cost Center C)
Friday PM (3 Hours to Cost Center C)

It doesn't matter what days used a certain cost center as long the hours worked match their percentage. Until here there has been no problem. It is simple. The issue is when an employee reports time off. If the employee only worked 33 hours one week, I will need an adjusted percentage of all cost centers for the whole week maintaining the fixed 5 hours in the morning and 3 hours in the afternoon shifts. Ideally, I'd like to see if using excel, I can input the number of worked hours, and the number of hours of time off. Then, excel will populate the AM (5 hours) and PM (3 hours) calculating or assigning the cost centers to these hours for the week. It doesn't matter which day the cost centers can be applied. (Only keeping in mind that the time off hours needs to reflect the exact day when the employee was out) I hope this will make sense.

The hours worked can be reduced to half an hour (0.5) segment. Less than 30 minutes will go to the lower digit (ex. 6 hour and 15 minutes will be only 6 hour), and above 30 minutes will go to the next digit up. (Ex. worked 3 hours and 40 minutes, will be 4 hours). There is no overtime, so the hours per week cannot exceed 40 in total. I really don't know if this is possible nor I have been able to create a sample excel to upload. What will benefit me at work is to be able to have a template document where just entering the hours work, time off (hours in specific date/precise day), and employee name will auto populate the cost centers already calculated from the am and pm shifts. If anyone can guide me or give me an idea of how to make these calculations, I'd appreciate it. Soph.

 

[vletm]

Sophoslat
Before Your sample file - few questions in this file.
Can this challenge solve with VBA?

 

[Sophoslat]

Hi,
Looking at the file, "the week on column G" (rows 11 through 15) shows a normal work week of 40 hours. Moving to the following week, we need to have the whole distribution of a 40-hour week. For example, employee "Y" worked 33 hours and took time off on Friday for 7 hours. The allocation needs to be reflected on Friday May 13, the following: 7 hours of time off and only 1 hour worked. (Remember there are only 8 hours of work per day)
The cost center allocations for A, B, C will need to be adjusted accordingly by their percentages for the 33 hours from Monday - Thursday and one hour on Friday. I am not familiar with VBA, if you are suggesting creating a macro and running it that's fine.

I assumed the thinking behind the concept is to figure out how to do the calculation for all cost centers based in the distribution of 5-hours in the morning and 3-hours, in the afternoon, for a 40-hour work week, when an employee takes time off. (We can think of time-off as a fourth cost center)
I 'd like to see two cells to input worked hours and time off. then, the week work schedule will fill out the hours for the cost centers as you have noted on columns "H" and "I". I appreciate so much your help.

 

[vletm]

Sophoslat
From Your original
It doesn't matter what days used a certain cost center as long the hours worked match their percentage.
Is it really that 40hrs per week, which You're calculating?
There will be weeks then there could be automatic less than 40hrs (eg 30-May, 4-July ...).
Okay, normally - there will be 40hrs weeks, but what about those weeks?
eg 30-May - should employee still work 40hrs in that week?
Isn't there a plan, how do employees should work?
If employee would like to have some hrs off - then - that could record to the plan too.
Do You mean that if employee has kept 7hrs off previous Friday - then that action should show someway in past?
For me, if employee would like to have next week's Friday 7hrs off - then that could notice 'better'.
Your samples 5+3hrs - I copied it.
How many hours employee could work per day (max)?
How many hours employee should work per day (min)? ... if that day is working day.
How many parts could one working day split?
... do it matter if every hour has allocated to different cost center? A + B + C + A + B + C + A + B ?
Of course, that won't be the best way at all to allocate.

> I've worked a bit same kind of case for years - with three weeks periods, with many interesting rules. <
I know, You have clear rules - but without those - someone else could make/offer some different rules for You.
Please, do Your own sample version in Excel.

 

[Sophoslat]

Hi,
Is it really that 40hrs per week, which You're calculating?
Yes, every work week is 40 hours only. No overtime.
There will be weeks then there could be automatic less than 40hrs (eg 30-May, 4-July ...). Okay, normally - there will be 40hrs weeks, but what about those weeks?
No, all weeks need to add to 40 hours. Holidays and other events will be calculated as "Time Off".
eg 30-May - should employee still work 40hrs in that week?
For a month ending in the middle of the week, still will be 40 hours worked week, but calculated into two months.
Isn't there a plan, how do employees should work?
For payroll every week is consider a 40-hour work. (This will be time off, if employee takes a whole vacation week off)
If employee would like to have some hrs off - then - that could record to the plan too.
Hour off is considered time off, and calculated with the cost center for time off. (Ex. cost center "Z")
Do You mean that if employee has kept 7hrs off previous Friday - then that action should show someway in past?
The time-sheet runs Monday to Friday. There should be 8 hours on Friday regardless if the employee worked all 8 hours or had some hour of time off.
For me, if employee would like to have next week's Friday 7hrs off - then that could notice 'better'.
Still there is one hour that needs to be coded to a cost center. I cannot pick when to allocate the time off from the employee. This is the only requirement of the allocation that the time off needs to be on the exact day for the week that the employee was absent.
How many hours employee could work per day (max)? it is always calculated as 8 hours. (ex. all 8-hours worked, partial hours worked+time off, or all 8-hours’ time off)
How many hours employee should work per day (min)? ... if that day is working day. Every Monday to Friday it should be only 8 hours per day.
How many parts could one working day split? The hours can be divided into many parts as needed per day, to be able to allocate the cost centers. The only condition is having to be in a 30-minute (0.5 hour) increments.
do it matter if every hour has allocated to different cost center? A + B + C + A + B + C + A + B ?
It doesn't matter how the cost centers are allocated as long the keep the correct percentages per week.
I know, You have clear rules - but without those - someone else could make/offer some different rules for You.
Please, do Your own sample version in Excel.
This is the issue for me, I don’t have a clear idea how to create an excel that will allow me to calculate the weekly allocation of the cost centers from Monday to Friday for each employee. I’m doing manually every Monday in the morning but it takes many hours to do the calculation if an employee has taken any time off that week. I am just trying to see if excel and make me efficient with the calculations.
I’m attaching a sample excel of what I can think of it should look like, this example shows only a normal 40-hour worked week by one employee.

 

[vletm]

Sophoslat
... I just finish my next sample file,
which has some possible and ... not so useful variations (red texts).

I quick read Your above writing ...
You used new term: "Time Off"
Sometimes something will calculate into two months.
I tried to get possible max hrs per day - what could work?
... not which normally would calculate.
as well as - min hrs per day - what should work?
... Is it good to plan 1hr works? ... of course, if employee lives next to working place.
A + B + C + A + B + C + A + B ?
... really? Above means that after every hour have to go to next place to work.
About Your file...
... how do it works with those 'calculate into two months' cases?
I'll try to check Your file better later and reread Your writings.

Please, try to sit and hold Your breath then You'll open this file.
... as I wrote: which has some possible and ... not so useful variations (red texts).

 

[Sophoslat]

You are working hard. Let me clarify cost centers. These are not places. The employee comes to work every work day and works for 8 hours, 5 hours from 8 am to 1 pm and then works 3 hours from 2 pm to 5 pm, Monday to Friday. Cost centers are codes that Human Resources department needs to allocate be able to pay the employee salary, debiting those charges from an account (Accounting stuff). My job, is every Monday morning to allocate the proper codes in the time sheet from the previous week, making sure the codes (cost centers) keep their proper percentages for a week. This will be repeated every Monday and so on.

 

[vletm]

It was still some basic testing.
So ... as I thought ... those 'reds' were not so useful with Your case.
PLAN (H-column) could be like Your 'cost center Z' ... but my way used.
Of course, maybe 90% of weeks could be as normal - basic 8hrs working.
But - the last 10% ... those could solve someway as I did a sample.
... with formulas and manually.
Do it matter, which model match the best? week 1 to 6 - 1st full week has 2-May.
Eg for holiday, there could be own 'letter'.
Anyway, if PLAN has something - that will matters.
Are those 'Off hrs' always full hours?
If someone would work only afternoons (3hrs) is it okay? ... or only mornings (5hrs)?
... those hours employee is at work.
You wrote that ... to allocate the proper codes in the time sheet from the previous week ... hmm?
> What would You do if someone has worked different way?
... eg PLAN has to have 'holiday' and for some reason, employee has come to work ( of course, employee could do what ever ...)
> How would/could You use eg my sample file?
OR
employee has worked in other cost center? ...eg all the week = 40 hrs.
How do it works with those 'calculate into two months' cases?
... two months do not match with full weeks. ( only February could be exact four full weeks. )
How many employees do You check?
... Could You give some number?
... no need to be exact number ... eg 10-25-50-75-100... I don't want to count it from photo

 

[Sophoslat]

Hi,
It seems you are overthinking what I am looking for. Part of my job is to calculate every week the allocation of salary payment for each employee. The employee, every Friday afternoon, will submit the number of hours worked, each day, from Monday through Friday. Example: employee XXX submitted: Monday AM 5 hours, PM 3 hours, Tuesday AM 5 hours, PM 3 hours, Wednesday AM 5 hours, PM 3 hours, Thursday AM 5 hours, PM 3 hours, Friday AM 5 hours, PM 3 hours.

I will take these worked hours and calculate the allocation of cost centers codes. (As noted in a normal week 40-hour no vacation) Until here is very simple. I sent you a file showing the allocation.

The issue begins when an employee takes time off. Then, I manually need to come up with an allocation distribution based on all the percentages of the cost centers for hours less than 40. Then, I need to manually assign the AM and PM schedules the cost center's codes with the numbers of hours assigned to each cost center per day. Ex. Employee ZZZZ worked 31 hours and took 9 hours off (Vacation on Tuesday 8 hours, and 1 hours on Wednesday AM). I will need to assign the hours per cost center, in The AM and PM. (Keep in mind (No vacation) normal hours of 40 are allocated to: Cost center A=8 hours; Coat center B=14 hours, and Cost center C=18 hours per week, for a total of 40 hours/week)

If an employee worked only 31 hours the cost center distribution will be: Cost center A=6.2 hours; Coat center B=10.85 hours, and Cost center C=13.95 hours. Total cost centers = 31 + 9 hours vacation = 40 hours per week.

Monday (only 8 hours a day)​	Tuesday (only 8 hours a day)​	Wednesday (only 8 hours a day)​	Thursday (only 8 hours a day)​	Friday (only 8 hours a day)​
AM (only 5 hours)​	AM (only 5 hours)	AM (only 5 hours)	AM (only 5 hours)	AM (only 5 hours)
5 hours for Cost center A	5 hours (Time off)	1 hour (time off) 4 hours for cost center B	2.05 hours for cost center B. Plus, 2.95 hours for Cost center C	5 hours for cost center C
PM (only 3 hours)​	PM (only 3 hours)	PM (only 3 hours)	PM (only 3 hours)	PM (only 3 hours)
1.2 hours for cost center A, plus 1.8 hours for cost center B.	3 hours (time off	3 hours towards cost center B	3 hours for Cost center C	3 hours for cost center C.

As you can see, when adding the total of hours of the week for cost centers, they will match the wanted allocation. This took me around 35 minutes to complete this calculation, and this is only for one employee. I will also need to round the minutes based on 0.5, instead of having any other fraction hour. (Not done in my example above.

I hope this will help you to understand the kind of file I need to do all these calculations by just telling excel how many hours an employee worked, and how many hours took vacation (time off), excel will provide me with the information from the red font data in the table. Is it clear now?

 

[vletm]

Sophoslat
Seems You missed that my sent file with its writings...

Did You notice Right -Top -corner ? ... You should have something like above.
... but there should be something red too.

Your above writing could fill to it - and then it would look like below

Don't it have Your given Tue 8hrs Off and Wed 1hr Off?
Light blue row shows needed hours do to to get 8hrs per day.
( A 6:12 / B 10:51 / C 13:57 / Z 9:00 = those Off Hrs )

After that there could do many combinations to find the balance ... like in below:
You give Your variation ... but could it be like this?
#1 Mon A 5:00 / B 3:00 gives 8:00Hrs for Monday
#2 the rest of A hrs (1:12) could fill to Thu as 1:00
#3 Because C need still a lot of hrs ... fill Wed C 5:00 and Thu C 5:00
#4 Wed and Thu both will be 2:00 short ... and those could fill there
>> now, days Mon to Thu has all 8:00 ... okay?
#5 Fri is still empty ... there could fill 4:00 to both B and C
>>> then all day will have 8hrs ... okay?
Same time then You're filling those hrs, Your could see sums of each A/B/C as well as each days.
Now, this shows that A has 0:12 low (short) from original need (6:12).
as well as B has 0:09 high from original need (10:51)
as well as C has 0:03 high from original need (11:57) ... okay?
With below calculations could do final balance if needs.
I have used all hrs as times, then the results are shown as times.

About Your This took me around 35 minutes to complete this calculation
... my sample version takes ... about 1 minute.
Depends number of employees ... smoother?

Have I find out something?

As I wrote in other message,
if You could have useful text file, then all those calculations could do 'automatic'.
That means, You press the button and .. soon You would have do those calculations ready.

 

[Sophoslat]

You got it. I don't understand the text file comment. from "As I wrote in other message,
if You could have useful text file, then all those calculations could do 'automatic'.
That means, You press the button and .. soon You would have do those calculations ready. "

 

[Sophoslat]

What will be the formula calculation for the cost centers from the number of hours worked, to fill out the grid on your example?

 

[vletm]

Sophoslat
text file...
Do You see those employees given data from computer?
... or ... from paper?
If You see those data from computer ...
... then there are many times possible to save those data eg to .csv-file ( ~text-file ).
If so, then that .csv-file could use (after some modifications) with my sample file.
... then You no need to fill those 'Off hrs' ... or anything ... hmm?
... then 'all feeding procedures' could do automatic
and You could get 'the list' to next step.

Do You need that kind of output (per employee), which You saw with my previous reply?
or
something else?

Did You really wrote that about 35minutes?

 

[vletm]

What will be the formula calculation for the cost centers from the number of hours worked, to fill out the grid on your example?
If something like that needs then I would do it 'my way'.
... as I wrote as I've understood ... there could be many variations (with those blue value).

 

[Sophoslat]

Employes will email me their work hours at the end of the week. I manually write the hours in an excel file. Then I manually create the allocation, and yes, sometimes it takes me a while to do the math and the rounding for each day. After I get the allocation, I send it back to the employee for their records and submit the accounts payable for processing payment. I'm desperately looking to have a template excel file to just input worked hours and time off, have excel to do all calculations and then I will copy the grid with the allocated numbers and pasted in an email to be able to send it back to the employee.

 

[vletm]

Now, You're writing something about work hours ...
So far, You've written about Off hours.
Please, test this sample
#1 select with spin-button (left top) correct week to gray area
#2 if there are holiday's eg President's Day - those will come automatic
#3 fill other employees needed Off hours below Z
... You can only those fill five cells below Z
... if #2 gives Off hours then those no need to fill! ( as now, in this sample )
#4 press [ Allocate ]-button
#5 check results
Note: Depend [ Mode ] that calculation could be visual or not.
#6 here should be add option to 'print something out'
#7 repeat steps #3 to #6 as many times as You'll need

Maybe somewhere would add employees name?

 

[Sophoslat]

You will need to guide me to see how it works. How it works?
Where (what cell) should I input the number of work hours?
I see the up and down arrows to select the week. That's all I can figure out. Sorry

 

[Sophoslat]

Opps, I think I got it now, the only thing I need to do is to input the Z Column for time off and everything else gets allocated. I am speechless.

 

[Sophoslat]

I tested on week 17, no time off and it's showing a mistake, there are low hours for Thursday (8 hours) and Tuesday (1 hour). Did I mess up ?

 

[vletm]

Sophoslat
What is number right-bottom-corner? ... this has 143

I didn't test fully this calculation, there could be something to adjust.
... but You can press again [ Allocate ]

I figured next step - I'll try to do sample later.

 

[vletm]

Sophoslat
If You could have this kind of table
Where You could fill employees Off Hours if not 'Holiday Schedule'
... then those could use
and
after employee allocation will be solved
then You'll get employees PDF for use.

 

[Sophoslat]

I love what you did with the national holidays. very nice. I'd prefer something that looks like your original but a place for employee name and hours worked, then I can copy this information and send it back by email to each employee. see below:

 

[Sophoslat]

Sophoslat
If You could have this kind of table
Where You could fill employees Off Hours if not 'Holiday Schedule'
... then those could use
and
after employee allocation will be solved
then You'll get employees PDF for use.
View attachment 78783

Do you mean for me to use this format entering all employee hours and then run it to create the other view with the allocations? Using this table, it will need only to enter the time off like you have in red font? This is very promising.

 

[vletm]

That 'data'-sheet looks like below ( not ready ... sorry)
There could see same dates as 'calculation'-sheet.
You could add names and fill their Off Hours
By clicking [ NAME ]-header, You could [de]select all names.
By clicking single name, You could [de]select that name.
From 'calculation'-sheet, You press that [ Allocate ]-button to allocate selected (green) names
and same time You'll get PDF for employee - check other file - to send it ...
Files name shows Monday's date and employees name.

Here is the newest version.
Save it to one own folder and
create in same folder new folder, which name is PDF.
... then You could get those employees PDF.

 

[Sophoslat]

This is amazing. I was looking for some guidance on how to make my work more efficient, my expectations were to be able to come up with a formula to calculate some percentages, but you have created the most professional document beyond any of my expectations. You are amazing working with excel. Your approach is in target of what was needed. I'm so grateful for your work. Thanks a lot.