Variable Data Range Depending on Date

[joerv007]

Hi guys. I am after some assistance on something which might not be possible.

Scenario: I am making a spreadsheet to check regulation compliance with jobs that we do. Each date a job gets done, it goes in a list (one job per row). Some dates, we might do several jobs (but each job still gets its own row). There are different types of jobs (we'll call them A, B and C for ease). The Regulations require us to do a certain amount of jobs within a rolling 3 month period. What I am trying to do is to create a spreadsheet which will look back over the last 3 months and count the number of each different type of jobs. So if we have done the correct number of A jobs in the last 3 months, a cell will say something like "you are compliant". If we have not done enough, it would say something like "you are not compliant".

Problems: The issue is that I have to use a rolling 3 month time frame. If I was just using a static timeframe (for example Jan - Mar), I could just use the =countif formula. But sadly, the rolling period effectively means that my start date of the range is 3 months back from today's date.

So, my question is this. Is it possible to define a range of data which covers all the data in a list which falls within a 3 month period as defined from today's data going backwards? If this is possible, how can it be done?

I have done some research on this and understand that the offset function in the Name Manager might be useful but I cannot see how to define it to this extent.

[FDibbins]

Hi, welcome to the forum

I think you need to take a look at using COUNTIFS() with 1 of the criteria being <=TODAY() and another criteria being > EOMONTH(today(),-3). You could use another criteria to test for "done"

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.