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.
