Trend Data Analysis

[SamOwen92]

Good Afternoon, 

I am hoping to find a solution to my problem, however I may be asking excel to do to much. 

I have an absence excel spreadsheet, that has 12 sheets (one for each month) and is set our per employee. On each month you find the employee's line and enter either "S" - Sickness, "P" - personal, "H" - Holiday. However my manager's want some sort of analysis that would put years of data together that would tell them whether someone has a sick day the same time each year. Or if there is a trend that they have 2 sickness days then a personal day, the same time each year. I have only set this spreadsheet up this year (based off a template from the internet) so don't currently have historical data in the same format, however will be using the same format going forward with a new document each year. 

[#2]

@SamOwen92 

Did you ever work out how to track trends? as I would be interested in this, I'm only just looking into something like this myself but I just noticed your post and your image looks similar to how I want to have ours. 

Here is my list to do:

• One document for all the business
• Create Departments; Accounts, Sales, CAD, Laser, Press, Goods In/Out
• Create rules that possibly change cell colours when we have 6 planned absences on any given date
• Create rules that prohibit too many staff off in any are at one time; Accounts =1, Sales =2, CAD =1, Laser =2, Press =1, Goods In/Out =1
• Show the three absences differently; holiday, planned absence, sick
• Calculate the Bradford Score for sick (B = S * S * D) B = Bradford score, S = Total number of spells absent and D = Total number of days absent