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 Elsmar Cove Business Systems and Standards 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


Elsmar XML RSS Feed
Elsmar Cove Forum RSS Feed

Monitor the Elsmar Forum
Sponsor Links



Courtesy Quick Links

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


International Standards Bodies - World Wide Standards Bodies

ASQ - American Society for Quality

International Standards Organization - ISO Standards and Information

Howard's
International Quality Services

Marcelo Antunes'
SQR Consulting, and
Medical Devices Expert Forum

Bob Doering
Bob Doering's Blogs and,
Correct SPC - Precision Machining

NIST's Engineering Statistics Handbook

IRCA - International Register of Certified Auditors

SAE - Society of Automotive Engineers

Quality Digest

IEST - Institute of Environmental Sciences and Technology

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
Jen Kirley's Avatar
Jen Kirley

 
 
Total Posts: 5,871
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,542
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
Jen Kirley's Avatar
Jen Kirley

 
 
Total Posts: 5,871
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
Jen Kirley's Avatar
Jen Kirley

 
 
Total Posts: 5,871
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
Jen Kirley's Avatar
Jen Kirley

 
 
Total Posts: 5,871
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 Elsmar Cove Business Systems and Standards 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

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
Conditional Formatting for Microsoft Excel jesterjay8 Excel .xls Spreadsheet Templates and Tools 2 8th April 2011 02:44 PM
Excel Spreadsheet Conditional Formatting help needed samsung Excel .xls Spreadsheet Templates and Tools 12 25th November 2010 04:52 AM
Excel Cell or Text Formatting help smryan Excel .xls Spreadsheet Templates and Tools 27 28th September 2009 08:13 AM
How to get entire row in MS Excel colored? (Conditional Formatting) Ajit Basrur Excel .xls Spreadsheet Templates and Tools 5 5th September 2009 03:39 PM
Conditional Formatting - Excel amariepsu Excel .xls Spreadsheet Templates and Tools 8 28th July 2009 06:13 AM



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


 
 


NOTE: This forum uses "Cookies"