Conditional Formatting - 17648

Hi All,

I need help on conditional Formatting.

I have a column of data with future DATE, such as 2 Jan 09, 4 Des 09, etc
I would like to assign automatically different colors to those data that 
match these condition:

If (TODAY's date < Data's date) And more than 30 days,  COLOUR is Orange
If (TODAY's date < Data's date) And more than 60 days,  COLOUR is Yellow
If (TODAY's date < Data's date) And more than 90 days,  COLOUR is Green

If (TODAY's date >= Data's date) And more than 30 days,  COLOUR is Red


I would like to gave it AUTOMATIC, such that if I put a date, it will adjust 
the colour byitself in that instant.

I apperciate the help.

0
Utf
2/2/2010 4:42:01 AM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
1005 Views

Similar Articles

[PageSpeed] 22

Reply:

Similar Artilces:

Excel cells changes number format automatically
I am using Excel 2007. Many of the cells I am working on are in general format where I store various numbers for analysis. These values change itself to date format occassionally creating whole lot of work for me to change them back to general format. How can I solve this issue? Also in my pivot table, my source data is in general format. Still when I click on sub total in any catagory in the table to find details about it, formats are changed date format in the new sheet it pops up. Can someone help resolve this issue. ...

Custom Number Format problem
I need a special number format, but I can't seem to get what I want: IF a number is an integer, then there should be NO decimal point displayed. IF a number has a decimal part, then if should display only one digit for it. For instance: 3 -> 3 4.34 -> 4.3 2.88 -> 2.9 34 -> 34 5.01 -> 5.0 Is it possible to do this with a custom number format ? THANKS ! R P.S. I would prefer NOT to use any VBA to do this! Maybe. But easier would be: =IF(MOD(A1,1)=0,INT(A1),ROUND(A1,1)) and format as General Of course you can replace A1 with your original formula, but then it is cal...

Two Condition Vlookup
I have two worksheets that contain similar information. What I need is a formula that will lookup the Value of column A in worksheet one, but also there’s a second condition that I want it to meet which is in column B. So basically if these two conditions are met then I want to see the result from using worksheet 2 as the lookup reference table. Does anyone know the correct format for a two condition Vlookup? Any help is greatly appreciated. You have a few choices... One is to insert a new column A in the rates worksheet. Then you could concatenate the values in the new colu...

Formatting times
How do I format a column so that it shows hours:minutes:seconds:tenths when either hours or tenths may not be part of the data input? I'd also prefer not to have zero for the hours or tenths if they're not there. And why does the "input field" at the top of the screen show the number as a time even if I've formatted the cell as one of the other "time" choices? TIA bj That going to be hard using the Format Cells dialog box but suppose your times are starting in A1 then the following formula will do what you want: =TEXT(A1,IF(A1<1/24,"mm:ss.0"...

Format Currency
I have a table that contains values that are in a very unique format. The decimal and comma are incorrect...(Pls don't ask why, file is from Europe...) The decimal should be a comma and vice versa. Anyway, I need to know how to format (in a query) in such a way that if reflects correct currency. See example: Currently: Should Look Like: 2.301,44 $2,301.44 2.094,75 $2,094.75 44,72 $44.72 54,32 $54.32 15,96 $15.96 785,63 $785.63 2.356,89 $2,356.89 174,72 $174.72 Can anyone help? Thanks - I would try this: Select the range to fix edit|replace what: . (dot) with: x replace all T...

40 Col Receipt Format
I would like to create a receipt format which places the item code on a line, then drops a line and print the item description (my shop's item descriptions are quiote long). Can anybody help and also point me to where I can learn more about changing the receipt formats in more advanced ways such as add additional comment lines etc? hi, Go to receipt.xml and find the "Transaction Detail" block there you can replace the whole block with following (then attached in so manager again the receipt format) this sometime require as it will not apply automatically the change one.: ...

Paste format with Freeze Frame into an new worksheet
How can I take a spreadsheet from one worksheet and paste to a new worksheet but keep all of my columns, rows, and freeze frame set when I paste into the new worksheet? An exact copy into a new worksheet? Celeste, Instead of copy/pasting the cells, why not copy the sheet? Right-click the sheet tab, Move or Copy. You can specify New in the To Book box. Be sure to click "Create a copy" or it'll move the sheet out of the original workbook. That box should be at the top of the dialog. -- Earl Kiosterud www.smokeylake.com "Celeste" <Celeste@discussions.microsoft...

How do I keep formatting when creating a chart from a pivot?
I have a data source that I use to create various pivots and the I use thos pivots to create graphs. I replace the detail for the pivots each month and refreash each pivot which updates the graphs. The problem is the column and data values lose their formatting when I refreash the pivots. Is there a way to make the formatting of the graph stay? Hi, this is a well-known nuisance. One workaround is to record a macro while you do the formatting and then run the macro after you refresh the pivot data. regards, teylyn Kevin;674443 Wrote: > I have a data source that ...

Formatting cells in Excel
Help! My ability to format a cell in Excell has suddenly disappeared! Any date I input will only show the serial number format and I cannot get it to change. What can I do? Sounds like you are in "View Formula" mode. Hit CTRL + `(above Tab key) to toggle off/on. Gord Dibben MS Excel MVP On Fri, 16 Jun 2006 11:30:02 -0700, jdcc63 <jdcc63@discussions.microsoft.com> wrote: >Help! My ability to format a cell in Excell has suddenly disappeared! Any >date I input will only show the serial number format and I cannot get it to >change. What can I do? Gord --...

Excel with Concatenate does not format date correctly
I am using Excel 2007. I want to use concatenate to combine employee name with date started. The date is not showing correctly. A1=Jane Doe B1=10/8/2011 C1=CONCATENATE(A1,B1) The screen shows: Jane Doe40824 What do I need to do differently, so it says: Jane Doe10/8/2011 Use : =A1&" "&TEXT(A2,"mm/dd/yyyy") Daniel > I am using Excel 2007. I want to use concatenate to combine employee > name with date started. > The date is not showing correctly. > > A1=Jane Doe > B1=10/8/2011 > > C1=CONCATENATE(A1,B1) > The screen shows: > Jane ...

How do I add a zero in front of a number in general format?
When I type a number in the "General" format with a zero in front of the numbers and move to the next cell, the zero disappears. I want the zero to be in front of the number. How do I fix this? Format the range with the required number of zeroes, such as 000. -- HTH RP (remove nothere from the email address if mailing direct) "dvs" <dvs@discussions.microsoft.com> wrote in message news:450FED75-F6AF-4D78-9C86-7AF86443F813@microsoft.com... > When I type a number in the "General" format with a zero in front of the > numbers and move to the nex...

Email report in PDF Format using a Macro
Using a macro I can generate and export files in pdf format. Then I have to create an email, attach the report and send. Is there a way to email a report in pdf format directly from Access utilizing a macro? I don't have an answer for you but a question. I am trying to do what you have accomplised but don't know how. Thanks "lreecher" wrote: > Using a macro I can generate and export files in pdf format. Then I have to > create an email, attach the report and send. Is there a way to email a report > in pdf format directly from Access utilizing a macro? Once ...

CRM 3.0
Hi, On the Order form we have created a new attribute called CCExpiryDate and while creating it you only get the formatting options of Date or DateTime, and there does not seem to be a way to change the format of a field, like in say Access or Excel to display or even allow entry of MM/yy. It is only dd/MM/yy. Is there any way to change how it can be entered or even the display? Thanks! Shauna It's not possible to change the input or display format, unless you're willing to create a lot of JavaScript code or to use custom controls that mimic the behavior of the CRM date cont...

Can I change from a poster setting to a smaller format?
Select File, Page setup and select the size you want. -- Don Vancouver, USA "lisa" <lisa@discussions.microsoft.com> wrote in message news:E9D52FC1-F839-4AAF-9F62-C89B6822BB96@microsoft.com... > ...

Formating Fiscal Quarters, rather than Months
I would like to be able to format a date based upon fiscal quarter, rather than simply Month year. This would be based upon the underlining data: For example, what I would like to see: Q1FY06 Q2FY06 Q3FY06 Q4FY06 Would be created by inputing: 11/1/2005 02/01/2005 05/01/2005 08/01/2005 Once formated they would show as above. Otherwise, I will define a named field, and go that way, but I have a lot of these headings to do, and formatting them would be simplest way to get them to work, if possible. Is there any sort of custom formatting that would do this? Thanks! PatK...

Format Background Color Teaser
lo to med. important, not sure. When using "Fill Color", or other method you may suggest, Is there a way to make a default background color, that can be changed temporarily / goes back to original color if clearing "fill color". (e.g. if base color 25% grey, then red, yellow or light orange). text may or may not be present in cell. Is there an entry for Custom Background Colors?? / will that work, danke schone in advance -Nastech trying: conditional format (insuf): row() conditional format (sloppy): =OR(LEFT(F8,2)="b ",LEFT(F8,3)="bb ") ...

Format Records alternating colours.
Hi I would like to have aternating rows differnet colours. eg. customer no 123 red 123 red 456 green 456 green 456 green 789 red 789 red Can you tell excel to do this? mike Try this: Insert a blank (helper) column before your data. (I'll assume it will be Col_A. Col_B will list customers) A2: =IF(B2=B1,A1,NOT(A1)) Copy A2 down as far as you need Select from B2 down and to the right as far as you have data ...with B2 as the active cell. From the Excel Main Menu: <format><conditional formatting> Condition_1: Formula is: =$A2 Click the [Format] button ....select the Re...

Conditional Formatting #100
Hi all I am not sure if this is possible but I have a spreadsheet with two columns of dates. If the date in one column is less than the one in the other column I want to highlight the column in a different colour. However the dates are all different Example 12/04/05 13/05/05 04/01/05 13/04/05 05/05/05 03/03/05 Therefore I have tried to put an if statement in the conditional formatting but it gives me an error. I could create a separate IF statement column but this becomes a bit clumsy (there's actually loads of dates on different sheets) Therefore in summary I want t...

Date Format 04-28-10
I have a spreadsheet that contains a list of dates but they are formatted as follows - 20100427. How would I go about converting those cells to a standard format - 04/27/2010? Any help or suggestions would be appreciated. Use a formula such as this to convert it to a date =DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2)) you can then format the date however you wish using standard XL formats. -- HTH... Jim Thomlinson "readystate" wrote: > I have a spreadsheet that contains a list of dates but they are formatted as > follows - 20100427. How would I g...

simple format question
i have the following entered as the format for a text box: (000) 000-0000" x"000 but am getting a display that looks like this: (000) 212-6757 x760 when it should be: (212) 675-7760 The field has number data type and the last 3 digits (the phone number extension) is not always included (I suspect that i may need to break the extension out to a seperate field (?) any thoughts would be greatly appreciated -mark Mark Kubicki wrote: >i have the following entered as the format for a text box: >(000) 000-0000" x"000 > >but am ...

Inbox file in OE changed to .dbt format
Help: I can't access one of my inboxes in OE. All of a sudden it dissappeared-- so I went to the identities folder and found that it was there but had been changed into an Outlook file with a .dbt extension. How do I change it back so I can still use it in OE? Any help will be greatly appreciated, Rob@holo-source.com Hi, Rob - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an O...

Cells with time format and calculating the diffrence
I have formatted 2 cells say A1 and B1 as time and chose the first type being in the form 13:30 - This shows up in the equation line as 1:12:00 PM. So I type in 13:12 and I get what is required BUT is there a way that I can type 1312 without having to put the ':' in? and still get it to produce the time equation format for my 3 rd cell C1 which will be to calculate the minutes difference between the 2 first cells eg. 13:12 and 13:20 giving the 3rd cell as 0:08 . I have used =+B1-A1 for the C1 cell. Also the other thing is that when I wish to calculate the minutes between the 2...

Pivot table Format Style Sheet Capability
I have been using Excel pivot tables for a number of years. It is a great capability. One feature that I would really like to see is the ability to modify the Auto Format style sheets. For example it would be nice to change the color scheme or the font used on one of the Auto Format options and then be able to save that change as an option you could select. I am a power user and would be happy to test this capability if developed. One other thought. It would be really neat if you could have conditional formatting within the pivot table. For example show the sales forecast for the...

How to export a query to then xml-format file via VBA?
Hi. I would like to transfer my access query to xml-format, but how can I do it via VBA automatically? hanski ...

Publisher and image formats #2
My client is a publisher user. I have created an illustration for him scanned/prepped in Photoshop. He wants to receive the image with no background. Can he place an EPS file? Or a photoshop file with embedded channel or clipping path? Does Publisher have something like Runarounds? Thanks for suggestions, Lost in your world. carli007 <carli007@discussions.microsoft.com> was very recently heard to utter: > I have created an illustration for him scanned/prepped in Photoshop. > He wants to receive the image with no background. Can he place an EPS > file? Yes, and when printed ...