Forum Discussion
Formula for calculating employee work hours when the list is constantly changing
I'm sorry to say it, but it's hard to know where to begin.
Your main problem is not rows 158 and 159. You could fix those formulas today, but still have to come back next month, or the month after. There are formulas in your workbook that could serve as textbook examples of formulas that might work now, but are prone to error and next to impossible to maintain; Cell AW4 in the Apr 2023 tab, for example.
The main problem is that the whole workbook is designed --- this is a guess -- based on a paper ledger sheet paradigm: a different sheet for each month, a different column for each employee, different sections in each sheet for each project. That kind of design, that way of thinking about how to collect/organize data like this, inevitably leads to the kinds of difficulties you're having.
It is not a database. If it were designed as a database, with daily entries that track employees working hours per project (as well as any other attributes that need to be tracked), it would be easy to extract all these individual totals and summaries on a monthly basis, YTD basis, project by project basis--whatever. Excel is really good--REALLY GOOD--at extracting and summarizing data from databases. But setting it up to do that requires thinking about the inputs and outputs from the start with a different mindset.
It doesn't make me feel good to say this, and I'm not looking for work -- I'm a retiree who happened, when working, to have been the director of a major corporation's HR and payroll database, so my observation is based on working experience with the kind of information you're dealing with. I am in this forum simply because I enjoy helping people resolve difficulties with Excel. My suggestion to you would be to find a consultant--maybe there's somebody in your organization (from the looks of it, you're part of a large organization of some kind)--who can redesign this from scratch, to give you a robust approach to collecting the data (Input) and reporting on it (Output). Excel might not even be the best software--Access or another database system might be more suited--but if it stays in Excel, it will be far more reliable with far fewer, if any, month to month revisions such as you're now encountering.
To reiterate what rzaneti has already said, please give us an idea of how your data are organized in that WIP spreadsheet. And a copy of the file (with real names replaced by, say, Disney characters) would be far more helpful than a mere image. Post that copy on OneDrive or Google Drive and paste a link here that grants edit access to the file.
In the meantime, a question as well: are you wanting to calculate the hours per employee or hours per project or some other combination?
In your post you are asking for a formula; one of the realities of Excel is that there are frequently--and expect this will be no exception--...there are frequently several different ways to get from raw data to desired conclusion. And some of them take advantage of very recent developments in Excel, so it would also be helpful to know what version of Excel you're working with, in particular, whether its Excel 2021 or newer, or a Microsoft 365 subscription.
@Lilawney
Wow! That is hideous.
It is possible to make progress with data presented as crosstab forms, particularly if you are using Excel 365. The first thing to note from a formula such as
=AVERAGE(D156,'Jan 2023'!D149,'Dec 2022'!D157,'Nov 2022'!D158)is that the row numbers are different for each term you are averaging over and, if the employees list changes, even the column letters might be different. Since corresponding items appear with different cell references, I would conclude that the practice of direct cell referencing is inappropriate (I tend to refer to direct cell references as abominations that have no place in any computational environment and are even of dubious value on a second rate town plan -- but few would agree with me!)
If your summary block on each sheet were specified as a local named range
'Apr 2023'!EmployeeHeader ='Apr 2023'!$C$149:$AD$149 'Apr 2023'!SummaryBlock ='Apr 2023'!$C$150:$AD$159then you would at least stand a chance of developing a formula that would select corresponding data items to combine. With 365, one would specify a Lambda function
= Productivity(1, "Adam") where the Lambda function "Productivity" is defined by = LAMBDA(month,employee, LET( monthSummary, CHOOSE(month, 'Apr 2023'!SummaryBlock, 'Mar 2023'!SummaryBlock), monthProductivity, CHOOSEROWS(monthSummary, -4), employeeHdr, CHOOSE(month, 'Apr 2023'!EmployeeHeader, 'Mar 2023'!employeeHeader), employeeMonth, XLOOKUP(employee, employeeHdr, monthProductivity), employeeMonth ) )Whilst this might offer a solution to your problem, I suspect that the 'cure' will prove worse than the disease.
- 100% (lol); when I was first given this spreadsheet to fix, I knew that I wouldn't know how to with how poorly laid out everything is in the workbook. Thank you for providing a temporary solution while my coworkers figure out what they want to do!
I'm sorry to say it, but it's hard to know where to begin.
Your main problem is not rows 158 and 159. You could fix those formulas today, but still have to come back next month, or the month after. There are formulas in your workbook that could serve as textbook examples of formulas that might work now, but are prone to error and next to impossible to maintain; Cell AW4 in the Apr 2023 tab, for example.
The main problem is that the whole workbook is designed --- this is a guess -- based on a paper ledger sheet paradigm: a different sheet for each month, a different column for each employee, different sections in each sheet for each project. That kind of design, that way of thinking about how to collect/organize data like this, inevitably leads to the kinds of difficulties you're having.
It is not a database. If it were designed as a database, with daily entries that track employees working hours per project (as well as any other attributes that need to be tracked), it would be easy to extract all these individual totals and summaries on a monthly basis, YTD basis, project by project basis--whatever. Excel is really good--REALLY GOOD--at extracting and summarizing data from databases. But setting it up to do that requires thinking about the inputs and outputs from the start with a different mindset.
It doesn't make me feel good to say this, and I'm not looking for work -- I'm a retiree who happened, when working, to have been the director of a major corporation's HR and payroll database, so my observation is based on working experience with the kind of information you're dealing with. I am in this forum simply because I enjoy helping people resolve difficulties with Excel. My suggestion to you would be to find a consultant--maybe there's somebody in your organization (from the looks of it, you're part of a large organization of some kind)--who can redesign this from scratch, to give you a robust approach to collecting the data (Input) and reporting on it (Output). Excel might not even be the best software--Access or another database system might be more suited--but if it stays in Excel, it will be far more reliable with far fewer, if any, month to month revisions such as you're now encountering.
- No worries! I knew that the layout and design of this spreadsheet would make it difficult to accomplish this goal; this design has been used since the company started. I will tell my co-workers their options. Thank you for your help!
- Of course! Let me just remove all sensitive data!
