Feb 17 2021 10:57 AM
Feb 17 2021 10:57 AM
Hello. The attached photo shows the average hours a month an employee may work (excludes holidays and weekends). But say I know an employee will be traveling for sales in Mar for 44 hours is there a way that I can get this chart to add in the hours and redistribute the remaining hours so that they always total 1750? To divvy up the 1750, I first divided by 12, then 3 for the two bigger categories (Marketing and Sales), then divided by 2 for HR and then divided by 2 again for Customer Relations and Budget. Don’t want to use a Gantt chart
Feb 17 2021 02:20 PM
Is it possible for you to post not an image but the actual spreadsheet you created as the basis for that image?
It would help us help you if you did it that way. Otherwise, beyond just telling you, 'Yes, it's possible" if you want some actual help in creating the formula...please, a spreadsheet.
And for the sake of clarification: are you saying you'd be putting in 44 instead of the 48 that appears there now? And would it be fair or safe to assume that you could also be asking "if any of the other numbers change as well, what would need to be done in order for the bottom left cell to read 1750?" Or is it only the Sales employee who might vary from the standard 48?
In other words, is it that the 1750 needs to be held as a constant goal to be achieved, with the other hours somewhat malleable? And are there limits to how many can be malleable, or how much malleability is allowed?
Can you tell us more about the bigger picture here? I used to be Director of HR and Payroll systems for a major corporation. I don't recall ever being asked to create a sheet like this. Ever. It almost seems like a case of the tail wagging the dog. Why aren't we just totaling actual hours?
Feb 17 2021 04:19 PM
@mathetes I attached the file. In regards to the next question: I have a calendar where I plan out the travel by week and then total the hours by month, so it would be 44 hours for that week on Sales which would change the redistribution of hours. Currently the monthly hour is 48 for Sales, but now with the travel it might change the number to 54: 44 for the travel and then 10 for the redistributed number amongst the categories. You are correct in that I’m asking if travel occurred in other months at various number of hours (those would inputted first) and what is the distribution of the remaining hours for that month/year. It doesn’t need to be exactly 1750, could go as high as 1800 but no lower than 1750.
I can’t disclose too much on the bigger picture but what I can say is that I do this for multiple people across multiple fiscal years so I’m just looking for a faster way.
What I Really want is to say something like this:
I just want to plug in travel dates, determine how many hours that would consist of (excluding holidays/weekends/other pto), total up the total travel hours by month/category, and it would recalculate the total number is hours for each category/month and equal close 1750.
Feb 17 2021 05:05 PM
Feb 17 2021 08:16 PM
If you have the backup data, the daily travel schedules or whatever, would you be willing to post that? I think that what you really want is not only possible, but more desirable, as you seem to be saying yourself.
Let me ask: is this single page something you do for each individual to somehow represent a year's worth of work hours? If not that, what is it? What do these numbers represent? What's the reality behind them?
I ask not to pry, but because of my background in HR and payroll systems. But also, maybe even more so, because of my desire to design spreadsheets that make sense. And you seem to be saying that the way you've been doing things doesn't totally make sense. Please forgive me if I'm misrepresenting you on that; I'm just trying to make sense of this.
Feb 17 2021 09:40 PM - edited Feb 17 2021 10:29 PM
It's essentially used to determine yearly costs for each department.
I am not able to post the travel schedule but this really the meat of the information that I need to generate, everything else is secondary and has to be entered manually.
Feb 18 2021 06:22 AM
I'm going to try one more time to make sense of all this.
Again, I'm probing this because from all you've said, what you have presented us with (at least from my perspective as a former HR/Payroll database manager) first needs to be addressed from the perspective of policy and practice; only later as an Excel question. Are we reflecting reality or primarily coming up with budgeting estimates? Either is legitimate, but which is it? Keep them separate.
Feb 18 2021 06:42 AM
@mathetes you are correct in that these are projections but when the time comes they are used to compare our actual expenses by, by month/ by category. Because things change so quickly the numbers do change on a monthly or even weekly basis depending so having something that can easily update number of hours would be really helpful. We do track actuals. Let me know if it can be done. Thanks.
Feb 18 2021 07:24 AM
If we were able to sit down face-to-face this would be a lot easier. I feel like you keep giving just little glimpses of what the full process is, and that is frustrating to me, perhaps in the same way that my incessant questions are frustrating to you.
For example, your latest reply makes me want to know how that monthly /weekly comparison happens and which numbers get changed and how. What you've shown--the spreadsheet you wanted help with--is SO totally homogenized--every number the same going down the column--that can't possibly reflect the reality.
So does IT get changed? How? And, for that matter, WHY? If that was just the preliminary projections for the year, why do those preliminary projections per se need to get changed. In all of my experience with budget/actual comparisons, the budget doesn't get changed itself. Variances get noted and approved or not. But you don't change the budget so that it matches the actual.
But then, as I try to look at your sheet itself, if you ARE going to revise a single number in a single column (March Sales goes from 48 to 44 was your example) does that change have to increase every other number from March (or is it April) forward, changing every other number by 4/1750? Or 4/(whatever balance of the 1750 remains)). Only the Sales numbers? Your description sounded as if every number in all columns (From April forward) was to get modified upward so as to keep the total close to 1750. But that seems (to me at any rate) so trivial as to be written off as a rounding error. More comprehensively, if ALL numbers in other columns are adjusted upward does that mean you're going to expect other people to work longer hours to compensate?
You see, I hope, that there's still quite a bit remaining to be defined, and that's after we are able to determine what it actually is that we're doing. I've been trying to see how your spreadsheet could be modified, and there those questions above are what has resulted.
And I'm afraid I've got a big meeting coming up later today and for the rest of the week--seriously--I'm retired but I'm a trustee on the board of a large national non-profit, and we have a board meeting beginning later today for which I need to finish preparing.
So maybe another person will chime in, but in the meantime, I really hope you'll take some time to reflect on and think through the process as a whole, the full process of which that presenting spreadsheet is just a small part.
Feb 18 2021 07:34 AM
@mathetes I understand this is frustrating, believe me but I can’t share everything else that goes into this. This is the biggest portion and the part that takes the longest so I’m just looking for this portion, everything else is easy. Thanks for the help.
Feb 18 2021 08:58 AM
@mathetes right now I just need this: a spreadsheet where I can enter someone’s name, their travel dates (converted into hours minus holidays, weekends, etc) and then the remaining 1750 hours gets redistributed where Marketing and Sales gets roughly 30% of the labor, Budget and Customer Relations get 8% and HR gets 15%
Feb 18 2021 09:13 AM
As I've said, glimpse followed by glimpse followed by glimpse.
This last was the first time you've mentioned "I just need this: a spreadsheet where I can enter someone’s name, their travel dates (converted into hours minus holidays, weekends, etc) and then the remaining 1750 hours gets redistributed where Marketing and Sales gets roughly 30% of the labor, Budget and Customer Relations get 8% and HR gets 15%"
I wish you well, sincerely.
You need to provide anybody who's going to help a far more complete picture of the data you have access to. This could, in fact, be fairly simple, I suspect. Maybe not. But Excel can work with lots of raw data to summarize and report. You've been tantalizing us with only the tip of the iceberg. You describe it as the major part, and maybe it is--I don't doubt that it takes you a lot of time the way things are structured--but that spreadsheet you've shared, the one you want help on, is dependent on OTHER pieces of data, as your last post makes ever so clear.
Feb 18 2021 10:33 AM
@mathetes Sorry I didn't make this clear up front. I have attached a spreadsheet that I hope helps. The first tab is a mock travel schedule similar to the one that I actually use. There you will see a list of the Employees, the Category that they are traveling under, the dates of travel and a crude calculation of the numbers of hours for that length of travel (would like it to exclude holidays/weekends, and other PTO).
The Labor tab then shows a pivot of the total hours and when they are occurring. Then the table to the left (I'm using Angela as an example), I've entered in her 2 hours for Mar and Jun, and then calculated the remaining hours that are needed for the remaining months and manually entered them into the table. I am looking to automate the table based on the number of hours from the travel schedule. Hope this is clearer. Please advise.