Shown here is the conditional formatting of cell backgrounds. The operator clicks on Format to select the cell color. Source: Levinson Productivity Systems P.C.


Problems can arise in factories that use 3 by 5 cards to keep track of gages. In fact, calibration problems are one of the top sources of nonconformances during ISO 9000 audits. Commercial software is available for managing a calibration program, but for small shops an Excel spreadsheet may do just as well. Learn how to program a spreadsheet to remind personnel when calibrations are due.

The Spreadsheet

Table 1 shows the layout of a simple spreadsheet for tracking gages and their calibration status. Colored cell backgrounds serve as visual controls that make each gage’s status easily visible to the person who manages calibration control.

The current date in cell B3 is given by the today function in Microsoft Excel. The lead time is entered by the operator, and it is the desired advance warning prior to the due date. In this case, gages may be calibrated up to 30 days before their deadlines. DUE with a yellow background appears when the gage is within 30 days of its calibration deadline.

The gage status is calculated by the following formula in cell D6, which also was copied into the subsequent cells.
    =IF(2*(C6<$B$3)+1*(C6-$D$3<=$B$3)*(C6>=$B$3)=0,“OK”,
    IF(2*(C6<$B$3)+1*(C6-$D$3<=$B$3)*(C6>=$B$3)=1,“DUE”,“OVERDUE”))

  • 2*(C6<$B$3) equals 2 if the due date is less than (i.e., earlier than) today’s date. If this is true, the gage calibration is overdue.

     

  • C6-$D$3<=$B$3 if the due date is within $D$3 days (in this case 30 days) of today’s date. C6 must also be greater than or equal to (later than or equal to) today’s date or else the gage is overdue. If both conditions are met, then 1*(C6-$D$3<=$B$3)*(C6>=$B$3) is equal to 1.


Quality System Considerations

The Excel spreadsheet is easy to program and is usable on any computer with Microsoft Office. Corel QuattroPro can be programmed similarly if the factory uses this software.

It is important to remember, however, that the spreadsheet is a quality record: a fourth-tier document under the control of a third-tier work instruction or second-tier procedure. For example, additional fields (columns) might be added to the spreadsheet to show the gage’s location, or the name of the person performing the calibration. It is vital to remember that a column heading is an implied instruction to enter information. Therefore, the spreadsheet and its controlling document must ask for exactly the same data to comply with ISO 9000’s provisions; conflicting instructions from controlling and subordinate documents constitute a nonconformance.

The same principle applies to how early the calibration may be performed; the controlling work instruction or policy must specify how many days ahead of deadline the gages may be calibrated. If this differs from gage to gage, a separate column must be added to give each gage a unique lead time. The cells in what is currently column D must then be reprogrammed to reference each gage’s lead time instead of a universal lead time. As a quality record, the calibration tracking spreadsheet also is subject to ISO 9000’s record retention provisions. The importance of backing up this file cannot be overemphasized, and the backup file must be kept in a place where it cannot be destroyed by an accident that erases the primary file, such as an offsite computer or in a computer media fire safe.