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

Jen Kirley

Quality and Auditing Expert
Leader
Admin
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!
 

Al Rosen

Leader
Super Moderator
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

Trusted Information Resource
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
Leader
Admin
Tim Folkerts said:
I could attach a simple example if it would help...Tim F
Let me fool with it a bit and see what I come up with.

Thanks guys!

Always learning...
 

Jen Kirley

Quality and Auditing Expert
Leader
Admin
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

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

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
Leader
Admin
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
Leader
Admin
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!
 
Top Bottom