yes,
you can use conditional formatting and colour the cells - or in a new column - flag they are not in compliance with text
BUT, if you can provide
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.
Then its possible to show you various formulas
there maybe be better ways to set out the data - but again I dont know how many certificates are needed
Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.
we can use today() to see todays date and then depending on how old , flag the cell - so if the certificate needs to be renewed say every year
=cell with date <= today-365
that will flag if less than 365 days - BUT we can use DATE(year(today())-1,month(today()),day(today()) )
to go back exactly 1 year - or go back x years or x months
Therefore -
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.
This will possibly enable a quicker and more accurate solution for you.
MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
XL2BB - Excel Range to BBCode
Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...www.mrexcel.com
You can also test to see if it works ok, in the "Test Here" forum.
Test Here
Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
OR if you cannot get XL2BB to work, or have restrictions on your PC
then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
Make sure you set any share or google to share to everyone
| Coleman_Practice Matrix-ETAF.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Bloodborne Pathogens | Hearing Conservation | Respirator Trainning (All Employees) (Fit Test as needed) | |||||||||
| 2 | ||||||||||||
| 3 | ||||||||||||
| 4 | RBT-JAN | RBT-FEB | RBT-FEB | |||||||||
| 5 | 2/29/24 | Monthly RBT Items | Client Required Items | Tracking | Some Items are Position Specific | Annual | Annual | Annual | ||||
| 6 | Name | Hire/Trans Date | Crew | Position | Department | Emp # | In Compliance | Bloodborne Pathogens | Hearing Conservation | Respirator Trainning (All Employees) (Fit Test as needed) | ||
| 7 | Employee 1 | A1 | Toolpusher | Drilling | 7623 | FALSE | 31-Dec-24 | 3-Feb-25 | 3-Feb-25 | |||
| 8 | Employee 2 | A1 | Toolpusher | Drilling | 14605 | FALSE | 30-Dec-24 | 3-Feb-25 | 3-Feb-25 | |||
| 9 | Employee 3 | A1 | Driller | Drilling | 13311 | FALSE | 30-Dec-24 | 3-Feb-25 | 3-Feb-25 | |||
| 10 | Employee 4 | A1 | Driller | Drilling | 12132 | FALSE | 30-Dec-24 | 3-Feb-25 | 3-Feb-25 | |||
| 11 | Employee 5 | A1 | Driller Trainee | Drilling | 10325 | TRUE | 30-Dec-24 | 3-Feb-25 | 3-Feb-25 | |||
NEW Matrix | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A5 | A5 | =TODAY() |
| H6:J6 | H6 | =H1 |
| G7:G11 | G7 | =OR( COUNTIF(H7:BA7,"")>0, COUNTIFS(H7:BA7,"<"&TODAY(),H$5:BA$5,"<>"&"once")>0 ) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10 | Cell Value | between NOW() and NOW()+30 | text | NO |
| AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10 | Cell Value | <NOW() | text | NO |
| H7:H9 | Cell Value | between NOW() and NOW()+30 | text | NO |
| H7:H9 | Cell Value | <NOW() | text | NO |
| AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23 | Cell Value | between NOW() and NOW()+30 | text | NO |
| AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23 | Cell Value | <NOW() | text | NO |
Thank you so much for your assistance.ok, so you can see blanks using
=COUNTIF(H7:BA7,"")>0
and then the dates are less then today()
=COUNTIF(H7:BA7,"<"&TODAY())
BUT need to also make sure the ONCE is not counted
=COUNTIFS(H7:BA7,"<"&TODAY(),H$5:BA$5,"<>"&"once")
if the date in the "once" cell is blank - it will be flagged by the previous condition
so use an OR()
=OR( COUNTIF(H7:BA7,"")>0, COUNTIFS(H7:BA7,"<"&TODAY(),H$5:BA$5,"<>"&"once")>0 )
can be used in conditional formatting
then you will get TRUE or FALSE
or can be combined in an IF
a small extraction for XL2BB
Coleman_Practice Matrix-ETAF.xlsx
A B C D E F G H I J 1 Bloodborne Pathogens Hearing Conservation Respirator Trainning (All Employees) (Fit Test as needed) 2 3 4 RBT-JAN RBT-FEB RBT-FEB 5 2/29/24 Monthly RBT Items Client Required Items Tracking Some Items are Position Specific Annual Annual Annual 6 Name Hire/Trans Date Crew Position Department Emp # In Compliance Bloodborne Pathogens Hearing Conservation Respirator Trainning (All Employees) (Fit Test as needed) 7 Employee 1 A1 Toolpusher Drilling 7623 FALSE 31-Dec-24 3-Feb-25 3-Feb-25 8 Employee 2 A1 Toolpusher Drilling 14605 FALSE 30-Dec-24 3-Feb-25 3-Feb-25 9 Employee 3 A1 Driller Drilling 13311 FALSE 30-Dec-24 3-Feb-25 3-Feb-25 10 Employee 4 A1 Driller Drilling 12132 FALSE 30-Dec-24 3-Feb-25 3-Feb-25 11 Employee 5 A1 Driller Trainee Drilling 10325 TRUE 30-Dec-24 3-Feb-25 3-Feb-25
Cell Formulas Range Formula A5 A5 =TODAY() H6:J6 H6 =H1 G7:G11 G7 =OR( COUNTIF(H7:BA7,"")>0, COUNTIFS(H7:BA7,"<"&TODAY(),H$5:BA$5,"<>"&"once")>0 )
Cells with Conditional Formatting Cell Condition Cell Format Stop If True AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10 Cell Value between NOW() and NOW()+30 text NO AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10 Cell Value <NOW() text NO H7:H9 Cell Value between NOW() and NOW()+30 text NO H7:H9 Cell Value <NOW() text NO AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23 Cell Value between NOW() and NOW()+30 text NO AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23 Cell Value <NOW() text NO
also added the full spreadsheet to dropbox as a share , but only on share for a few days
BUT if you want to flag that somethign is due to run out
you could change today()
to maybe a week before or a month before
today()-7
if that does not work , please explain why and give expected results
| Coleman_Practice Matrix-ETAF.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| F | G | H | I | J | |||
| 1 | Bloodborne Pathogens | Hearing Conservation | Respirator Trainning (All Employees) (Fit Test as needed) | ||||
| 2 | |||||||
| 3 | |||||||
| 4 | RBT-JAN | RBT-FEB | RBT-FEB | ||||
| 5 | Some Items are Position Specific | Annual | Annual | Annual | |||
| 6 | Emp # | In Compliance | Bloodborne Pathogens | Hearing Conservation | Respirator Trainning (All Employees) (Fit Test as needed) | ||
| 7 | 7623 | FALSE | x | N/A | 3-Feb-25 | ||
| 8 | 14605 | FALSE | 30-Dec-24 | 3-Feb-25 | 3-Feb-25 | ||
| 9 | 13311 | FALSE | 30-Dec-24 | 3-Feb-25 | 3-Feb-25 | ||
| 10 | 12132 | FALSE | 30-Dec-24 | 3-Feb-25 | 3-Feb-25 | ||
NEW Matrix | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H6:J6 | H6 | =H1 |
| G7:G10 | G7 | =OR( COUNTIF(H7:BA7,"")>0, COUNTIFS(H7:BA7,"<"&TODAY(),H$5:BA$5,"<>"&"once")>0 ) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10 | Cell Value | between NOW() and NOW()+30 | text | NO |
| AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10 | Cell Value | <NOW() | text | NO |
| H7:H9 | Cell Value | between NOW() and NOW()+30 | text | NO |
| H7:H9 | Cell Value | <NOW() | text | NO |
| AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23 | Cell Value | between NOW() and NOW()+30 | text | NO |
| AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23 | Cell Value | <NOW() | text | NO |
