Microsoft Excel - conditional formatting
Asked By Sarah May Dona on 14-Feb-12 12:33 AM
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.
Sreekumar P replied to Sarah May Dona on 14-Feb-12 12:58 AM
smr replied to Sarah May Dona on 14-Feb-12 01:20 AM
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:
That gives you the MONTH. Now put this in C1:
Now you have the YEAR. Put it all together in D1:
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:
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:
Format that cell as a number, and you're all set. If you need more help with conditional formatting, see this link:
Reena Jain replied to Sarah May Dona on 14-Feb-12 01:27 AM
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.
Try this and let me know
Somesh Yadav replied to Sarah May Dona on 14-Feb-12 01:43 AM
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
- 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
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
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:
- To check if a date is in the current week:
- To check if a date is in the current month:
- To check if a date is in the last 30 days:
[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.
C D replied to Sarah May Dona on 14-Feb-12 02:09 AM
In your Excel sheet ,
Colum B is actual Date
Column C is Date after 3 month
Date after 3 Month using following formula
Now applied Formating on Column B as following way
C1 > Today() then Red
C1< Today() then Green
Check ZIP file for more detail
Hope this is helpful !
Pichart Y. replied to Sarah May Dona on 14-Feb-12 12:23 PM
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
Hope this help.
Excel 2007 format a cell Excel
Hi, Does anyone know how to format a cell automatically ? I have a cell that changes fill color if its a weekday (eg. A1) but is there a easy Use the same formula for the conditional formatting formula that you used for the first cell. Depending on what you're doing, just using the absolute address ($A$1 instead of
Conditionally format a cell using a list of data defined in a rang Excel
Is it possible to conditionally format a cell using data defined in a range on a seperat tab? The data sheet information being list that I would like to turn the different color totals around 30 names Example: Cell A1 is "Larry" Cell A2 is "Bill" Cell A3 is "Fred" - Any cell that contains Fred, Bob, or Frank
How do I format a cell to allow me to input minutes, seconds, and hundredths of a second? Excel Miscellaneous Discussions JayD (1) Hundredths (1) Have you looked at Custom mm:ss.00 ? keywords: format, a, cell description: How do I format a cell to allow me to input minutes, seconds
cell format -> remove cell format pattern without effecting colors Excel
cell format -> remove cell format pattern without effecting colors Excel , remove, cell, format, pattern, without, effecting, colors" / > Is it possible to remove a pattern from a cell row without effecting the (multiple) cell colors Excel Miscellaneous Discussions VBA (1) XlSolid (1) Interior
cell(color, a1) Excel if A1 format = F2- = cell(color, a1) displays 1 if A1 format = G = cell(color, a1) displays 0 = cell(color, a1) therfore does not refer to the cell color but rather to the format
How do I format a cell to blink in Excell? Excel
Does anyone know of a way to format a cell to blink in Excell? Excel Miscellaneous Discussions BlinkingText (1) RE (1) Excell (1) Format (1) Blink (1) Cpearson (1) Student (1) Larry (1) see: http: / / www.cpearson.com / excel BlinkingText.htm - - Gary''s Student gsnu200709 keywords: How, do, I, format, a, cell, to, blink, in, Excell? description: Does anyone know of a way to format
How can I format a cell to automatically assign a new number? Excel
How can I format a cell to automatically assign a new sequential number each time I open up the excel spreadsheet _ Sheets(1).Range("A1").Value + 1 ThisWorkbook.Save End Sub keywords: How, can, I, format, a, cell, to, automatically, assign, a, new, number? description: How can I format a cell to
Custom format a cell Windows 7
How do I custom format a cell to display the following format sequence of Alpha-neumeric such as: XXXXXX-XX-XXXX ? My sequence is now displayed as groups / forums might assist you better. Thomas - - Thomas Lee (email@example.com) keywords: Custom, format, a, cell description: How do I custom format a cell to display the following format
How do I format a cell in feet? Excel
How can I format a cell so that when I enter 12.5 it shows 12'6"? I am trying to do this so in another cell I can multiply the length by another number. Excel Programming Discussions Excel (1) SUBSTITUTE (1 formula to convert the value 12'6"" to the numeric value 12.6 in another cell. . . = - -SUBSTITUTE(SUBSTITUTE(A1, "'", "."), """", "") or you can use this. . . SUBSTITUTE(SUBSTITUTE(A1, "'", "."), """", "") directly in a calculation
How to format a cell from date to week? Excel
Dear all, How to format a cell date like "2009-12-10" to year / week format "YYYYWW" = 200950? Also how to determine number of weeks between 2 weeks like from 200950 LEFT (1) YYYYWW (1) Year (1) Week (1) Int (1) You cannot achieve this with cell formatting. Excel does not support weeks in a date format. To change a date to