Elsmar Cove Forum Header Graphic The Elsmar Cove Wiki More Free Files The Elsmar Cove Forums Discussion Thread Index Post Attachments Listing Failure Modes Services and Solutions to Problems Elsmar cove Forums Main Page Elsmar Cove Home Page
Miner's MSA (Measurement Systems Analysis) Blog 
Go Back   The Elsmar Cove Forum > 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

Wooden Line

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

Wooden Line
Search the Elsmar Cove
Search Elsmar
Monitor the Elsmar Forum
Follow Marc & Elsmar
Elsmar Cove Forum RSS Feed  Marc Smith's Google+ Page  Marc Smith's Linked In Page   Marc Smith's Elsmar Cove YouTube Page  Marc Smith's Facebook Page  Elsmar Cove Twitter Feed
Elsmar Cove Groups
Elsmar Cove Google+ Group  Elsmar Cove LinkedIn Group  Elsmar Cove Facebook Group
Donate and $ Contributor Forum Access
Courtesy Quick Links

Links that Elsmar 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
  #1  
Old 1st May 2006, 03:22 PM
Jennifer Kirley's Avatar
Jennifer Kirley

 
 
Posts: 5,542
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
  #2  
Old 1st May 2006, 03:28 PM
Al Rosen's Avatar
Al Rosen

 
 
Posts: 3,547
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

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

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

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

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

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

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

 
 
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 Elsmar Cove Forum > 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
Supplier Rating Excel .xls Spreadsheet form sxbalasu Supplier Quality Assurance and other Supplier Issues 41 24th January 2013 11:20 AM
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 ISO/TS 16949 - International Automotive Quality Systems Standard 10 27th October 2009 03:15 PM
Within Part Variation - Excel .xls calculation spreadsheet shahshahani Excel .xls Spreadsheet Templates and Tools 11 29th June 2005 01:36 PM
Seeking: Simple Excel .xls SPC spreadsheet jager Excel .xls Spreadsheet Templates and Tools 15 30th October 2003 03:38 PM



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


   


Marc Timothy Smith - Elsmar.com
8466 LeSourdsville-West Chester Road, Olde West Chester, Ohio 45069-1929
513 341-6272
NOTE: This forum uses "cookies".