Formatting an Excel .xls spreadsheet to alarm out-of-calibration status

Jen Kirley

Quality and Auditing Expert
Staff member
Admin
#1
Suppose a program has spreadsheet worksheets with their lists of gages, their cal due dates and other calibration-relevant data and the supervisors are responsible to know when their worksheet's gages are coming due. The practice of browsing a list of gages to find an out-of-calibration date could be improved upon, I think.

For example, I have seen cells that turn red under certain parameters (some kind of logic function) and I think it's cool but I haven't figured out how to do that. Can anyone tell me how it's done?

I have had trouble with logic functions using dates. Other than Julian date (which would work) can a cell be so formatted to show a message "Danger Will Robinson!" (okay, I'm kidding about the message) based on a past-due date?

Thanks in advance!
 
Elsmar Forum Sponsor

Al Rosen

Staff member
Super Moderator
#2
Jennifer Kirley said:
Suppose a program has spreadsheet worksheets with their lists of gages, their cal due dates and other calibration-relevant data and the supervisors are responsible to know when their worksheet's gages are coming due. The practice of browsing a list of gages to find an out-of-calibration date could be improved upon, I think.

For example, I have seen cells that turn red under certain parameters (some kind of logic function) and I think it's cool but I haven't figured out how to do that. Can anyone tell me how it's done?

I have had trouble with logic functions using dates. Other than Julian date (which would work) can a cell be so formatted to show a message "Danger Will Robinson!" (okay, I'm kidding about the message) based on a past-due date?

Thanks in advance!
Look up "conditional formatting" in Excel Help. It will explain it
 

Tim Folkerts

Super Moderator
#3
Jennifer,

Use the "Conditional Formatting" option on the "Format" Menu.

The simplest method is to compare the cell value to a specific number. For instance, you could apply a format when ever a number is less than zero, or when it is between -3 and +3.

The next option is to campare to another cell. For example, you could calculate the upper control limit and then compare a series of cells to that cell.

The final option (I I just recently discovered) is to create a formula as a condition. When the conditional formatting box is open, look at the left most drop-down menu and choose "Formula Is". For example, if you select a range (starting at cell B4) and type the formula

=B4<TODAY()-30

it will test if something is 30 days past due. If you add a second condition like

=B4<TODAY()+7

it can test if it is coming due in the next week.

After you set the condition, you can have the cell color change by selecting "Format", the "Borders". Perhaps a month over due is red, slightly overdue is orange, and due in the next week is yellow.


I could attach a simple example if it would help...

Tim F
 

Jen Kirley

Quality and Auditing Expert
Staff member
Admin
#5
The color formatting (I used red for 1st formatting: past due, and gold for 2nd formatting: dates within a week of due date) didn't work when the referenced date was more than a month away from any of the listed due dates.
 
T

tomvehoski

#6
Try this:

Condition 1:

Cell Value Is Less Than =TODAY() Format is Red

Condition 2:

Cell Value Is Less Than =TODAY()+7 Format Gold

Once a condition is met, Excel stops evaluating further conditions. So even though a past due gage is also less than today + 7, Excel never checks since condition 1 was met. Make sure you enter the = sign.
 
D

Dave Dunn

#8
If the gages are listed in a long worksheet and have the calibration due date listed, you could sort the worksheet using the due date. That way you not only could find the ones that are overdue, but you could keep track of the ones that are coming due.
 

Jen Kirley

Quality and Auditing Expert
Staff member
Admin
#9
Dave Dunn said:
If the gages are listed in a long worksheet and have the calibration due date listed, you could sort the worksheet using the due date. That way you not only could find the ones that are overdue, but you could keep track of the ones that are coming due.
Yes, and in fact the due dates are in order from oldest onward.

It would be very helpful to somehow signal a message when a gage falls out of date. As it is, I understand maybe a macro could be written to alert via e-mail, but without that it still comes down to someone looking that the spreadsheet--which may be happening in time, or not; and is heeded, or not.

In such a case it lands us back again--(gasp!)--in the lap of employees following procedures. :cfingers:
 

Jen Kirley

Quality and Auditing Expert
Staff member
Admin
#10
tomvehoski said:
Try this:

Condition 1:

Cell Value Is Less Than =TODAY() Format is Red

Condition 2:

Cell Value Is Less Than =TODAY()+7 Format Gold

Once a condition is met, Excel stops evaluating further conditions. So even though a past due gage is also less than today + 7, Excel never checks since condition 1 was met. Make sure you enter the = sign.
This worked. Thank you!
 
Thread starter Similar threads Forum Replies Date
W Excel Number Formatting - Auto Decimal Place by What You Type Excel .xls Spreadsheet Templates and Tools 16
J Conditional Formatting for Microsoft Excel Excel .xls Spreadsheet Templates and Tools 2
S Excel Spreadsheet Conditional Formatting help needed Excel .xls Spreadsheet Templates and Tools 12
Ajit Basrur How to get entire row in MS Excel colored? (Conditional Formatting) Excel .xls Spreadsheet Templates and Tools 5
A Conditional Formatting - Excel Excel .xls Spreadsheet Templates and Tools 8
smryan Excel Cell or Text Formatting help Excel .xls Spreadsheet Templates and Tools 27
Ajit Basrur Need help in Conditional Formatting for the entire row Excel .xls Spreadsheet Templates and Tools 3
S Process Documentation Formatting - Requirements Document Control Systems, Procedures, Forms and Templates 3
W General Linear Model - Formatting Data Six Sigma 3
B Can you guys help me figure out formatting a 510K Other US Medical Device Regulations 11
Q IT Instruction - use same formatting as ISO procedures? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 4
R Quality Manual - Formatting (print) Question Quality Management System (QMS) Manuals 6
J Incoming Inspection Records using Excel File ISO 13485:2016 - Medical Device Quality Management Systems 2
S 95% UCL - Attaching an excel for recovery study Statistical Analysis Tools, Techniques and SPC 0
T EXCEL - How do I make the cell stay blank until a date is entered in N7? Excel .xls Spreadsheet Templates and Tools 6
Ed Panek Validating Excel (XLS) formulas 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 7
C Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
R Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
S Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Ajit Basrur Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 9
J Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
P Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
I Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23
M Matching two matrices in Excel Excel .xls Spreadsheet Templates and Tools 5
J Level 3 KPI Excel Template Manufacturing and Related Processes 1
bobdoering Informational New AIAG PFMEA Process - Excel Template Attached FMEA and Control Plans 23
GreatNate Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
E Part 11 Compliance, Excel living documents (i.e. document master list, equipment list, approved supplier list) Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 3
J ISO 9001:2015 Excel Gap Analysis wanted ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
B AS9110 Audit Check Sheet wanted Excel or Word Document AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
G Excel report with pictures Excel .xls Spreadsheet Templates and Tools 5
A Query regarding MS Excel usage Excel .xls Spreadsheet Templates and Tools 2
A Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3
D Asterix when pasting a column of values from Excel into Minitab (V17) Using Minitab Software 5
Y Excel Automation - Printing Word Documents from within Excel Excel .xls Spreadsheet Templates and Tools 0
S Help me build an Excel Spreadsheet for tracking training Training - Internal, External, Online and Distance Learning 14
P Template or example Excel list of ISO certificates issued to clients ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
M Medical Device Process validation, Validation of excel spreadsheets used for process Other Medical Device and Orthopedic Related Topics 4
C Excel date format from Denmark to USA Excel .xls Spreadsheet Templates and Tools 6
G ISO 9001:2015 - Is control possible with Excel adaptable measurement sheets? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 24
N Where I can find an Excel sample/template for recording Delivery Time and Cost Excel .xls Spreadsheet Templates and Tools 2
D How do I go about creating document logs and registers with the MS Excel Excel .xls Spreadsheet Templates and Tools 2
D Microsoft Excel database to Stand-alone software Calibration and Metrology Software and Hardware 3
Q Controlling Multi-Tab Microsoft Excel Forms Excel .xls Spreadsheet Templates and Tools 5
B Zeiss CMM Calypso output to Excel or CSV file Help Inspection, Prints (Drawings), Testing, Sampling and Related Topics 17
S used excel formula calculated the ARL's with Rule1&Rule2 for Shewhart control chart Statistical Analysis Tools, Techniques and SPC 0
N Gantt Chart / Time Tracker in Excel - Combining Tasks on the Same Line Excel .xls Spreadsheet Templates and Tools 2
M Preventive Maintenance Spreadsheet in Excel Quality Assurance and Compliance Software Tools and Solutions 2
1 KPI Dashboard Template in Excel Format wanted Excel .xls Spreadsheet Templates and Tools 9

Similar threads

Top Bottom