Hi Folks
I'm working on a spreadsheet for our local lifeboat station to give visual reference to a rolling group of exercise dates for revalidation over an 18 month period for sea going lifeboat crew
Basically, there are 15 exercises that crew have to complete within an 18 month timeframe and what I wish to do is run conditional formats from the date inserted in the box that conditionally formats it green if 12 months old or less, Amber if greater than 12 months but less than 18 months and Red if greater than 18 months (gives crew and management time to target specific exercise or enquire as to why crew member has not been able to complete designated exercise with specific timeframe.
At moment I have the setup as follows. (Don't worry about the 18 in the ALB Anchoring and Veering section. This was just to help me visualise the number of months for the conditional formatting)
As you can see, I can get it to work for less than or equal to 12 months (Green) and greater than or equal to 18 months (Red - which is a big No-No for Crew) but for the life of me, I can't see to work out how to construct the conditional format to be be greater than or equal to 13 AND less than or equal to 18 (Warning - Amber).
At moment it is showing Red as the date is greater and equal to 18 months and it works if I set a date in cell F3 where the number of months is less than or equal to 12 (Turns Green)
Any assistance greatly appreciated
Declan
I'm working on a spreadsheet for our local lifeboat station to give visual reference to a rolling group of exercise dates for revalidation over an 18 month period for sea going lifeboat crew
Basically, there are 15 exercises that crew have to complete within an 18 month timeframe and what I wish to do is run conditional formats from the date inserted in the box that conditionally formats it green if 12 months old or less, Amber if greater than 12 months but less than 18 months and Red if greater than 18 months (gives crew and management time to target specific exercise or enquire as to why crew member has not been able to complete designated exercise with specific timeframe.
At moment I have the setup as follows. (Don't worry about the 18 in the ALB Anchoring and Veering section. This was just to help me visualise the number of months for the conditional formatting)
As you can see, I can get it to work for less than or equal to 12 months (Green) and greater than or equal to 18 months (Red - which is a big No-No for Crew) but for the life of me, I can't see to work out how to construct the conditional format to be be greater than or equal to 13 AND less than or equal to 18 (Warning - Amber).
At moment it is showing Red as the date is greater and equal to 18 months and it works if I set a date in cell F3 where the number of months is less than or equal to 12 (Turns Green)
Any assistance greatly appreciated
Declan
| New OCF Exercise Training and Service Log 2021.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Station Notes | ALB Plan Start Date | ALB Lifeboat | ||||||||||
| 2 | ALB PPE, SAR Unit Layout and Pyros | ALB Towing | ALB Mooring and Berthing | ALB Helming and SAR Unit Handling | ALB Anchoring and Veering | ALB Emergency Procedures | ALB Search and Rescue | ALB Navigation | ALB Helicopter Operations | ||||
| 3 | 01/04/20 | 18.00 | |||||||||||
OCF ALB Exercises | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3 | J3 | =DATEDIF(F3, TODAY(), "M") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F3 | Expression | =DATEDIF(F3, TODAY(), "M")>=18 | text | NO |
| F3 | Expression | =DATEDIF(F3, TODAY(), "M")<=12 | text | NO |
