Help with a dashboard/reporting tool

[grimupnorth]

Hi all, first time poster, I hope starting a thread without a certain post count isnt against the rules?

My quandry is as follows:

I have created a HR disciplinary recording tool called 'Disciplinary cases' basically as and when workplace disciplinaries are being undertaken the HR person involved adds or updates this worksheet. I have added in some reporting funcionality: 'Disciplinary KPI' - this is just a lot of Sumproduct's and countif's.

As it stands the report can only take into account all existing data, how could I go about reporting for specific time specifications (eg I want to see number of cases, counts of different primary allegations and average length of suspensions for all investigations between specific dates)

Also if I could filter by HR Lead/Directorate/Investigating Manager that would be great.

My initial thought was to create a primary key which would be populated by the lists, so for example HR Lead & Directorate & primary allegation = WS-OPS-IUP then I could use this key and have a new table populate in a worksheet showing only records where this key was present. This strikes me as convoluted and problematic though.

Ive looked at pivots and as amazing as pivots are they dont seem to give me what I need due to the date specifications.

Does anyone have any ideas on a good way to do some timeframe reporting from my original table 'Disciplinary cases', I was struggling with dates as it would have to show live records during the specified period, so id need a complicated function to pull up more than just records where the case either started or closed during the timeframe given (live ones, if that makes sense?).

please find attached example

data has been anonymized, any help would be greatly appreciated and you can be sure of me buying you a drink if we ever crossed paths.

[grimupnorth]

even some help with just the date issue, how can I query to get records that would have been open within a certain time frame?

im desperate