Calculating work in progress between two dates
Hi, sorry if this is a bit long. I support a team of engineers who take on consulting work from clients. Cases can be open for a few days but most take several months. Some of them can last more than a year.
I need to monitor the engineers’ caseloads so I’d like to produce a table showing how many open cases (work in progress) each engineer had each month over a rolling 12-month period. I have a spreadsheet which gives each job a reference number in column A, the engineer’s name and column B, the opened date in column C and the closed date in column D. The closed date obviously remains blank while the case is open.
My table has a row for each engineer’s name and columns for each month. The cells to the right of each engineer’s name will display how many cases they had open in each of the 12 months.
I’d like to define each month as a period of 30 days and use formulas to calculate start and end dates with the most recent period ending =today(). This means the date ranges are updated automatically and the table always displays a full 12 months of data.
The problem I have is that I can’t come up with formula for each cell in the table to calculate how many cases the engineer had open that month. Does anyone else monitor something similar and did you solve this problem? Hope someone can help.
|