Employee attendance record

[Brenkk]

I want to design an employee attendance record with conditional formatting that will high-lght the following

1. Ten consecutive days absent
2. Three or more separate periods of absence in a three month period
3. Five spells of separate periods of absence in a twelve month period.

What I would like is amber to high-light in Amber when an individual is approaching one of the above targets and Red once the target is reached.

I would appreciate any suggestions on how I would go about designing spreadsheet.

 

[anglais428]

I would keep it simple and set up three separate columns - 1 for each of you points. Are you planning on manually adding in if they are attending each day?
It may just be easier to keep an absent file with name, start date of absence, return date from absence, count of days absent.

 

[Brenkk]

I hope to have a single spreadsheet that I would update weekly with any absentees for that particular week.

Instead of having to check each record (row) for each individual employee I was hoping to use conditional formatting to identify when any of the above criteria are nearing the trigger poing (amber) or have reached the trigger point (red)

 

[VoG]

Cross-posted http://www.pcadvisor.co.uk/forums/index.cfm?action=showthread&threadID=396947&ForumID=1&sr=1