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!

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

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

I could attach a simple example if it would help...Tim FLet me fool with it a bit and see what I come up with.

Thanks guys!

Always learning...

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.

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.

Thanks Tom! I will try that this evening I think.

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.

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:

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!

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:

No problem. My company requires all 100,000 of our metrics be coded red/yellow/green so I am used to that trick. :caution:

What you write above is possible in Access, and probably Excel - although I have only done it in Access. I had a supplier corrective action database that would launch itself one morning a week (via a Windows scheduler utility) and run a macro (via a command line setting) and generate a report and automatically fax it. This was before we had e-mail (early 1990s). It really prompted people to return their late CARs since they thought someone was looking at every one and manually faxing them reports. Some finally caught on that I really was not at work at 6 am on the fourth of July.

Look into the visual basic function "SendMail" in Excel or Access for information on how to automate the send function.

Greetings.

Here is my solution in excel sheet. You can add some e-mail notification, but i think in this case is useless beacause excel sheet must be opened to start checking macro.