If formula for calibration file

  • Thread starter Thread starter Jale
  • Start date Start date
J

Jale

Hi all
I have a calibration check out file in excel 2002.

Equipment ID Cal start date Cal Finish Date. Result
a 13.10.1994 13.10.1995 old
a 03.11.1995 03.11.1996 old
a 25.11.1996 25.11.1997 old
a
a
a 13.10.2000 13.10.2001
calibration required
b 11.10.1993 11.10.1995 old
b
b
b 31.07.2003 31.07.2004 334
c 12.10.1992 12.10.1994 old
c

and go on.
IF same Equipment identity would be one ,below formula has been working OK.

=IF(K15-TODAY()>0,K15-TODAY(),"calibration required")

But I want to figured out to find last dated equipment identity write above
formula and the
others will be "old" shown....

Thanks in Advance

Jale
 
Using your sample data, array-enter (Ctrl+Shift+Enter) the following
formula in cell L15:


=IF(J15<MAX(IF($I$15:$I$23=I15,$J$15:$J$23,0)),"Old",IF(K15-TODAY()>0,K15-TODAY(),"calibration_required"))

and copy down to L23
 
You're welcome. You can use conditional formatting to colour the cells:

1. Select cells L15:L23
2. Choose Format>Conditional Formatting
3. Leave the first dropdown as Cell Value Is
4. From the next dropdown, choose "equal to"
5. In the text box, type: old
6. Click Format, choose Yellow on the Pattern tab, click OK
7. Click Add
8. Repeat steps 3 to 7 for calibration_required in red
9. For the third condition, choose Cell Value Is, greater than,
and type a zero in the text box; select the green pattern
10. Click OK

Note: I used an underscore in calibration_required so the formula would
stay on one line in the newsgroup posting. You can remove it.
 
many thanks again...


Debra Dalgleish said:
You're welcome. You can use conditional formatting to colour the cells:

1. Select cells L15:L23
2. Choose Format>Conditional Formatting
3. Leave the first dropdown as Cell Value Is
4. From the next dropdown, choose "equal to"
5. In the text box, type: old
6. Click Format, choose Yellow on the Pattern tab, click OK
7. Click Add
8. Repeat steps 3 to 7 for calibration_required in red
9. For the third condition, choose Cell Value Is, greater than,
and type a zero in the text box; select the green pattern
10. Click OK

Note: I used an underscore in calibration_required so the formula would
stay on one line in the newsgroup posting. You can remove it.
Click to expand...
 
Back
Top