Formula for calculating employee work hours when the list is constantly changing

[LilYawney]

In my WIP spreadsheet, I am also calculating how many hours each employee worked on a specific project to obtain the WIP hours for the month. I don't want to overthink it for hours and waste my time but what formula can I use to calculate those hours if the employees working changes often (i.e., when someone is hired or fired)?

[#2]

Hi @LilYawney ,

Could you please share a sample or a image of your WIP spreadsheet? (you can put fake data on that)

I can imagine some alternatives to solve it, but I need to understand how your data is distributed along the sheet. 

[#3]

@LilYawney 

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.

[#4]

@rzaneti 

So the main problem is in rows 158 and 159; they are referencing the numbers from different sheets. When a new employee is added or removed from one month to the next, the referencing cell for that employee isn't the same anymore. When we start a new WIP month, there's also the problem of having to go back and edit the formulas to include the previous month in the Billable and Productivity hours average. 

Hopefully that makes sense!

(Also, I know how fix the dividing by zero errors so there's no need to help with that problem!)

[#5]

Of course! Let me just remove all sensitive data!

[#6]

@mathetes and @rzaneti 

Here is a copy of my spreadsheet!

[#7 · Accepted solution]

@LilYawney 

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.

[#8]

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!

[#9]

@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$159

then 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.

[#10]

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!

[#11]

@LilYawney 

I took a further look at the problem.  It appears that the AFE does not accept sheet-local names so a 'plan B' would most likely be helpful.  Probably names such as 'Employees2201' that compound the content with the date would work better.  The named Lambda functions may also need to be adjusted to conform to the requirements of the helper functions one chooses.  I have tried a number of strategies in the attached.