Microsoft Excel - conditional formatting

Hi,
Please help how to format a cell such that when the expiry date is within three months it turns green and when it is expired it turns red? Thanks a lot.

Hi,


http://www.techonthenet.com/excel/questions/cond_format4.php

this may help u

hi

follow this example:

You have a column of credit card expiration dates that are TEXT in the format MM/YY. If they're all exactly MM/YY you can do this:

Assuming the EXP date is in A1, put this in B1:

=LEFT(A2,2)

That gives you the MONTH. Now put this in C1:

=RIGHT(A2,2)

Now you have the YEAR. Put it all together in D1:

=DATE(C2+2000,B2,1)

That should give you an actual DATE value with the first of that month. If you want the first day of the NEXT month then say this:

=DATE(C2+2000,B2+1,1)

If a card says the EXP date is 04/09 then that usually means it's VALID THRU 04/09 which means it actually expires 05/01/09.

Now that you have an actual DATE value, you can go ahead and perform normal CONDITIONAL FORMATTING on that field to change its color if it's less than 30 days away, for example.

To figure out how many days from now that card expires, just say this in E1:

=D2-TODAY()

Format that cell as a number, and you're all set. If you need more help with conditional formatting, see this link:

http://599cd.com/tips/excel/conditional-formatting?key=AllExperts

refer
http://en.allexperts.com/q/Excel-1059/2009/9/Change-colour-cell-expiry.htm
http://www.excelforum.com/excel-worksheet-functions/513695-can-excel-automatically-change-the-font-color-of-an-expired-date.html

Hi,

Select the range (say B1:B30) . Make sure cell B1 referred in the formula is the active cell in the selection.
Apply the below conditional formatting formula and select the desired formatting.

=--(SUMPRODUCT(--(EXACT($B$1:B1,B1))))>1

Try this and let me know

Hi ,
Conditional formatting is a very useful feature in Excel. You can use Conditional formatting to tell excel how to format cells that meet certain conditions. For eg. You can use conditional formatting to show all negative values in a range in red color. [Learn conditional formatting basics].

Excel 2007+ – Conditional Formatting Dates

In Excel 2007, MS introduced several useful shortcuts to conditionally format dates. When you select some cells and click on Conditional Formatting button on ribbon and select “Highlight cells Rules” > “A date occurring”, Excel presents you quick shortcuts to frequent date criteria. This list includes options to format,

• A Date if it is yesterday
• Today
• Tomorrow,
• In the last 7 days
• Last week
• This Week
• Next Week
• Last Month
• This Month
• Next Month

Using this feature, you can quickly format the dates in your data meeting certain criteria.

This is very useful in situations where you want to highlight for eg. sales in last week. As the dates change, the highlighted values change dynamically.

Apart from these predefined date conditions, you can define your own conditions using formulas.

Excel 2003 – Conditional Formatting Dates

Unlike Excel 2007, there are no shortcuts for conditional date formatting in Excel 2003. You have to rely on Conditional Formatting Formulas to do this.

What is a conditional formatting formula?
In excel you can use formulas to determine which cells get the special formatting thru conditional formatting. For eg. a formula like =A1>50 applied over the range A1:A10 will highlight the cells with value more than 50.

So, to check if the date in cell A1 is yesterday, you can write a simple formula like,
=TODAY()-A1=1. [help on TODAY formula]

Here are some formulas to get you started,

• To check if a date is in the last 7 days:
  =TODAY()-A1<7
• To check if a date is in the current week:
  =AND(WEEKNUM(A1)=WEEKNUM(TODAY()), YEAR(A1)=YEAR(TODAY()))
• To check if a date is in the current month:
  =AND(MONTH(A1)=MONTH(TODAY()), YEAR(A1)=YEAR(TODAY()))
• To check if a date is in the last 30 days:
  =TODAY()-A1<30

[Help on AND formula, MONTH formula, YEAR formula, IF formula]

Using above formula based conditional formatting you can easily determine if a date meets a given criteria and highlight it.

A Practical Application – Highlighting Repeat Customers

Let us say you run a small retail store. And you want to give special discounts to all the repeat customers. In your mind a repeat customer is someone who bought twice from you in last 30 days. (If the person bought twice but the gap between 2 purchases is more than 30 days they are not repeat customers).

Click on the below links to jump to relevant section.
Excel 2007+ – Conditional Formatting Dates
Excel 2003 – Conditional Formatting Dates

Hope it helps you.

Hello,

 

In your Excel sheet ,

 

Colum B is actual Date

Column C is Date after 3 month

 

Date after 3 Month using following formula

=DATE(YEAR(B1),MONTH(B1)+3,DAY(B1))

 

Now applied Formating on Column B as following way

 

C1 > Today() then Red

C1< Today() then Green



Check ZIP file for more detail



Test.zip

 

 

Hope this is helpful !

Thanks

 

 

 

 

 

Hi Sarah May Dona,

Normally when we say expired data for the production..it is the period of time, which 3 months represents 90 days. It does not vary to the calendar date, so we can put the condition like this....

• There are 3 conditions
  • if the cell in the column is blank, then fill White color
    • input this formula for condition =ISBLANK(D4), then set fill white color
  • if today() - date in the cell < or = 90 then fill green
    • input this formula for condition =TODAY()-D4<=90, then set fill green color
  • it today()-date in the cell > 90 then fill red
    
    • input this formula for condition =TODAY()-D4>90, then set fill red color 
• Find here attachment...-->CoditionalFormat.zip