Highlighting Dates above 58 years.

[shyam]

I have a list which contains names and years in Year and Month format. I need to highlight those data's which contains the years greater than 58 years. eg. 65 years and 00 months, 63 years and 05 months 59 years and 06 months etc.

Excel Workbook
	I	J
12	aaa	65-00
13	bbb	45-02
14	ccc	42-04
15	ddd	63-05
16	eee	30-08
17	fff	23-08
18	ggg	59-06
Sheet1

Is there any formula which can be used to highlight those rows OR through conditional formating?

Regards

Shyam

 

[VoG]

Try like this

Excel Workbook
	I	J
1	aaa	65-00
2	bbb	45-02
3	ccc	42-04
4	ddd	63-05
5	eee	30-08
6	fff	23-08
7	ggg	59-06
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J1	1. / Formula is =LEFT(J1,2)+0>58	Abc

 

[JamesW]

Hi Shyam,

Highlight J13:J18 and goto Format -> Conditional Format.

Enter this in the Formula Is: field.

=IF(LEFT(J2,2)+0>58,1,0)

 

[shyam]

Thanks Mr. VOG and Mr. James

Mr. James, there seems to be an error in the formula (cell J12 and not J2).

Regards

Shyam

 

[JamesW]

Oops, sorry - Typo.

Glad you got it working though.