Conditional Fomatting
Closed
irene wallin
Posts
1
Registration date
Tuesday December 31, 2013
Status
Member
Last seen
January 1, 2014
-
Jan 1, 2014 at 12:07 AM
gm2612 Posts 11 Registration date Monday December 23, 2013 Status Member Last seen January 1, 2014 - Jan 1, 2014 at 03:02 AM
gm2612 Posts 11 Registration date Monday December 23, 2013 Status Member Last seen January 1, 2014 - Jan 1, 2014 at 03:02 AM
Related:
- Conditional Fomatting
- Apply conditional formatting to the selected cells ✓ - Excel Forum
- Conditional formatting 3 color scale percentages ✓ - Excel Forum
- Conditional hyperlink excel ✓ - Excel Forum
- Conditional formatting based on date in another cell ✓ - Excel Forum
- Conditional formatting based on date ✓ - Excel Forum
1 response
gm2612
Posts
11
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
Jan 1, 2014 at 03:02 AM
Jan 1, 2014 at 03:02 AM
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
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
