Conditional Formatting

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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.


ABCDE
1Start DateExam1Exam 2Exam 3Exam 4
2Frequency (months)1224Once36
3Timeframe for completion (Weeks)12612
414/07/2020
510/10/2019
601/02/2020
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,122,855
Messages
5,598,476
Members
414,241
Latest member
JD18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top