Good afternoon valued excel experts, I respectuflly request your help!
I am creating a spreadsheet for work. We have many employees who have an insane number of courses that must be completed throughout the year, all with varying expiry lengths (in days). I have tried to simplify with a basic mock spreadsheet to explain what we want to achieve (attached).
Situation: I input a date for 'Driver' course completion, for Jack in cell B9.
What I require from the spreadsheet from this one input:
Thankfully,
Mikey.
I am creating a spreadsheet for work. We have many employees who have an insane number of courses that must be completed throughout the year, all with varying expiry lengths (in days). I have tried to simplify with a basic mock spreadsheet to explain what we want to achieve (attached).
Situation: I input a date for 'Driver' course completion, for Jack in cell B9.
What I require from the spreadsheet from this one input:
- The B9 cell will immediately turn green, if in date (after checking certificate validity length in days from B5).
- The B9 cell will turn red, once it expires the 365 day limit, as dictated by B5.
- The B3 cell will check all dates in the 'driver' column (B9:B11) and show how many are in date (true?).
- The B3 cell will turn green/red, if the 'work requirement' (B1) is met/not met. (eg. we require 2 drivers at all times, all 3 employees are in date for driving certificate = cell turns green).
Thankfully,
Mikey.
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Work Requirement → | 2 | 1 | 3 | 3 | ||
2 | |||||||
3 | People Trained → | ||||||
4 | |||||||
5 | Certificate Valid For (Days) → | 365 | 365 | 30 | 30 | ||
6 | Certificate Name → | Driver | Chef | Cleaner | First Aid | ||
7 | |||||||
8 | Employee ↓ | ||||||
9 | Jack | ||||||
10 | James | ||||||
11 | Jim | ||||||
Sheet1 |
Attachments
Last edited by a moderator: