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.

Attached Images

Caseload Monitoring.jpg (93.2 KB, 27 views)

Can you attached a workbook axample so we dont have create all the data?

Hi Purfleet, see attached workbook. Thanks for helping.

Attached Files

Caseload monitoring.xlsx (37.6 KB, 7 views)

I am coming up with very different numbers ot your grid, so want to make sure are counting the same thing.

You want open jobs for the period? So if a job was started in May 2020 and closed in september 2020 what job would count in May, June, July, August and September?

I make Franks count for the 31th of May 2020 to the 30th of June 2020 as 24 jobs open

Hi Purfleet, thanks for your help. The numbers in the table are fictional just to give an impression of what I’m trying to produce. The results I was getting were so far off the mark I had to give up and enter made-up numbers.

Okay see attached.

I have moved the data to a Data worksheet and removed the Merged cells fom the table grid as merged cells are evil (use centre accross selectiion instead)

You will need to test the formula using filters on the Data sheet to make sure my logic is what you are expecting

Also the date periods you are using as 30 days mean that the periods will start to become strange over time (as month 12 is - 8th of May to 7th of June?). It is easy enough to have a calculation that always works out the real months with EOMONTH - see the date fields on my Grid

Attached Files

Caseload monitoring_ar12.xlsx (40.9 KB, 10 views)

Thanks Purfleet, this is amazing. It will take me a while to check but the figures look right. I also like to EOmonths formulas and will start using them from now on - much better. Can't thank you enough for this Purfleet, cheers

Good stuff.

Let me know how you get on