Conditional Fomatting

[Closed]
Report
Posts
1
Registration date
Tuesday December 31, 2013
Status
Member
Last seen
January 1, 2014
-
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
-
I am trying to add conditional formatting to a spreadsheet. It is an employee list showing drivers lic and med cards expiration dates. I want to format it so that the cell will highlight at 30 and 60 days out.
example drivers lic set to expire on 1/15/2014 I want this to highlight red so I can make that employee aware his license is about to expire.

1 reply

Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014

Hi Irene,

In you worksheet, in a particular cell, say "D1", format it for date format, by Right Click / Format Cells / Number - you will get two boxes named Category and Type.

In Category, select Date, and in Type, select the 14-Mar-01.

Then, type this formula in the cell D1 =now()
After typing this formula, you will see today's date in the cell D1 as 1-Jan-14.

Then, select format painter from menu bar, select Cell D1, and paste it in Cell E1 and F1. Then, type the following formula in cell E1 =D1+30, and in F1 =D1+60

Then, select the entire range of cells you have the expiry date of Driv Lic, click conditional formatting, condition1 - select "Cell value", in next box select "less than", and in next box type the formula =$E$1. Select "Format" button in the box, select pattern, and click on "Red" color.

Then, select the entire range of cells you have the Medical Card expiry date, click conditional formatting, condition1 - select "Cell value", in next box select "less than", and in next box type the formula =$F$1. Select "Format" button in the box, select pattern, and click on "Red" color.


This formating will show any cells which expires within next 30 days for Driv Lic and 60 days for Med Card.

Hope this fullfils your requirement.

Enjoy
gm2612