Hi,
I posted a while back regarding some conditional formatting rules and the help recieved was very useful. However, I now have a spreadsheet (example below) that I am stuggling to adapt the learning/CF rules to.
Column A is the start date of an employee. Row 2 is how often an employee must sit each exam (in months); "once only" = no re-sit required. Row 3 is how long the employee can be with the compnay before they are classed as non complaint if they have not sat each exam (in weeks).
Using B4 as an example I require CF rules that do the following;
1. If B4 is blank and the date in column A is within the number of weeks specified in B3 from todays date (formatting yellow)
2. If B4 is blank and the date in column A is older than the weeks specified in B3 from todays date (formatting red)
3. If a date entered in B4 is not older than the months specified in B2 from todays date (formatting green)
4. If B2 specified once only and a date was entered in B4 (formatting green)
5. If a date entered in B4 is 8 weeks away from becoming older than the months specified in B2 from todays date (formatting amber)
6. If a date entered in B4 is older than the months specified in B2 from todays date (formatting red)
I hope the above makes sense and appriciate any support in advance.
I posted a while back regarding some conditional formatting rules and the help recieved was very useful. However, I now have a spreadsheet (example below) that I am stuggling to adapt the learning/CF rules to.
Column A is the start date of an employee. Row 2 is how often an employee must sit each exam (in months); "once only" = no re-sit required. Row 3 is how long the employee can be with the compnay before they are classed as non complaint if they have not sat each exam (in weeks).
Using B4 as an example I require CF rules that do the following;
1. If B4 is blank and the date in column A is within the number of weeks specified in B3 from todays date (formatting yellow)
2. If B4 is blank and the date in column A is older than the weeks specified in B3 from todays date (formatting red)
3. If a date entered in B4 is not older than the months specified in B2 from todays date (formatting green)
4. If B2 specified once only and a date was entered in B4 (formatting green)
5. If a date entered in B4 is 8 weeks away from becoming older than the months specified in B2 from todays date (formatting amber)
6. If a date entered in B4 is older than the months specified in B2 from todays date (formatting red)
I hope the above makes sense and appriciate any support in advance.
| A | B | C | D | E | |
| 1 | Start Date | Exam1 | Exam 2 | Exam 3 | Exam 4 |
| 2 | Frequency (months) | 12 | 24 | Once | 36 |
| 3 | Timeframe for completion (Weeks) | 1 | 2 | 6 | 12 |
| 4 | 14/07/2020 | ||||
| 5 | 10/10/2019 | ||||
| 6 | 01/02/2020 |
