The Cove Business Standards Discussion Forums More Free Files Forum Discussion Thread Post Attachments Listing Cove Discussion Forums Main Page
UL - Underwriters Laboratories - Health Sciences
Go Back   The Cove Discussion Forums > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools
Forum Username

Elsmar Cove Forum Visitor Notice(s)

Wooden Line

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


Search the Cove
Search the Site
Monitor the Elsmar Forum
Sponsor Links



Courtesy Quick Links

Links that Cove visitors will find useful in your quest for knowledge:

Howard's
International Quality Services
Marcelo Antunes'
SQR Consulting
Bob Doering's
Correct SPC - Precision Machining

NIST's Engineering Statistics Handbook
IRCA - International Register of Certified Auditors
SAE - Society of Automotive Engineers
Quality Digest Portal
IEST - Institute of Environmental Sciences and Technology
ASQ - American Society for Quality
Reply
 
Thread Tools Search this Thread Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
  Post Number #1  
Old 1st May 2006, 03:22 PM
Jennifer Kirley's Avatar
Jennifer Kirley

 
 
Total Posts: 5,737
Please Help! Formatting an Excel .xls spreadsheet to alarm out-of-calibration status

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!

Sponsored Links
  Post Number #2  
Old 1st May 2006, 03:28 PM
Al Rosen's Avatar
Al Rosen

 
 
Total Posts: 3,540
Quote:
In Reply to Parent Post by Jennifer Kirley

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
Sponsored Links

  Post Number #3  
Old 1st May 2006, 03:54 PM
Tim Folkerts's Avatar
Tim Folkerts

 
 
Total Posts: 972
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
  Post Number #4  
Old 1st May 2006, 04:00 PM
Jennifer Kirley's Avatar
Jennifer Kirley

 
 
Total Posts: 5,737
Quote:
In Reply to Parent Post by Tim Folkerts

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...
  Post Number #5  
Old 1st May 2006, 04:28 PM
Jennifer Kirley's Avatar
Jennifer Kirley

 
 
Total Posts: 5,737
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.
  Post Number #6  
Old 1st May 2006, 04:38 PM
tomvehoski

 
 
Total Posts: 944
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.
  Post Number #7  
Old 1st May 2006, 04:59 PM
Jennifer Kirley's Avatar
Jennifer Kirley

 
 
Total Posts: 5,737
Thanks Tom! I will try that this evening I think.
  Post Number #8  
Old 2nd May 2006, 12:10 PM
Dave Dunn's Avatar
Dave Dunn

 
 
Total Posts: 131
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.
Reply

Lower Navigation Bar
Go Back   The Cove Discussion Forums > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools

Do you find this discussion thread helpful and informational?


Bookmarks


Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Forum Search
Display Modes Rate Thread Content
Rate Thread Content:

Forum Posting Settings
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Discussion Threads
Discussion Thread Title Thread Starter Forum Replies Last Post or Poll Vote
Excel Spreadsheet Conditional Formatting help needed samsung Excel .xls Spreadsheet Templates and Tools 12 25th November 2010 03:52 AM
5 Whys Template - Excel .xls Spreadsheet jrubio TS 16949 - International Automotive Quality Systems Standard 10 27th October 2009 03:15 PM
How do we calculate the control chart by using excel .xls spreadsheet? amanbhai Excel .xls Spreadsheet Templates and Tools 4 23rd February 2009 10:46 PM
Basic TS 16949 Requirements in an Excel .xls Spreadsheet ranvir.jaryal Excel .xls Spreadsheet Templates and Tools 9 11th December 2006 06:33 AM
Within Part Variation - Excel .xls calculation spreadsheet shahshahani Excel .xls Spreadsheet Templates and Tools 11 29th June 2005 01:36 PM



The time now is 06:01 AM. All times are GMT -4.
Your time zone can be changed in your UserCP --> Options.


 


NOTE: This forum uses "cookies".