Forecasting expiry dates using a stacked bar graph /pivot table

Hello,

I have have four columns representing four different courses. Employees have to attend each course every 3 years. The data in the columns indicates when three years has passed since their last attendance.

If the expiry date has passed the cell will display "OUTSTANDING".

Some courses are not required for certain job roles so is represented by "NOT REQ".

I want to forecast how many people are due to expire on a rolling 6 months lookahead. Ideally I'd like a bar chart with 6 stacked bars representing the number of expiry dates in that month. The stacking of the bar would be divided in four to represent the courses. e.g. There is high count of Course A expiry dates in September so I'll book in a higher amount of Course A's that month

I assumed this would be straightforward through a pivot but cannot seem to get one to work.
- Is it something to do with the mix of text and dates in the data?
- Should I not be using a pivot and instead some sort of count function table? I attempted this but cannot seem to COUNTIF the dates based on their month & year simultaneously.

I feel like I may be overthinking this as have been looking at this data for the last 3 days and seem to be blinkered.

Thanks in advance

Hi jamesclahar,

Yes pivot tables can be tricky with different data types.
I suggest a different approach in the attached file.
On Sheet2, I simply convert your data in floating 30 day periods (not calendar month).
On the Dashboard, we use COUNTIF to check how many courses are due to expire in the next 6 month (6 30-day periods to be more precise).
This then can be displayed in a chart.

Trust this gives you some ideas...

Brilliant! Thank you, yes that's a looks a lot cleaner. Definitely a good idea to convert the data, was struggling to do anything with it in current form. I think I may be able to use the EDATE & TEXT functions to represent the rolling months as a month & year format. I'll need to have a thinkk.

Thank you for your help again ORoos