conditional formmating - formulas

In conditional formatting I would like a certain row of dates to turn a 
colour when a report is sue in 60 days and they a different colour when there 
due in 30 days.  Also included in the formula I need the colour to remain 
blank if there are no dates in the column
0
Utf
1/25/2010 3:19:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
541 Views

Similar Articles

[PageSpeed] 0

In A9 I have the date 1/Nov/2009
In B10 I have =A9+15 and this is copied across to L9
So I have a row of dates in 15 day steps
I selected A9:L9 and used this formula in the Conditional Formatting dialog
=A9>TODAY()+60   (colour red)
Then I added
=A9>TODAY()+30 (color blue)
So I want to know what dates are 60 days and 30 days  in advance of today's 
date
The last two cells with 31/Mar and 15-April got highlighted red
The two before that are blue
Note that I must put the 60 day one first; otherwise all the last 4 cells 
would be blue !

Need more help? Tell us what version of Excel and be a little clearer about 
your needs. Are the dates you mention the due dates of the report?
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"rebecca" <rebecca@discussions.microsoft.com> wrote in message 
news:06E0BF67-FF52-4DF4-A094-38D847AA264F@microsoft.com...
> In conditional formatting I would like a certain row of dates to turn a
> colour when a report is sue in 60 days and they a different colour when 
> there
> due in 30 days.  Also included in the formula I need the colour to remain
> blank if there are no dates in the column 

0
Bernard
1/25/2010 6:09:17 PM
Reply:

Similar Artilces:

Workflow Wait Condition Error
I have a simple manual work flow rule based on "wait 4 mos before" a contract end date. In testing the rule today, a contract end date of 12/31/07 date triggers the rule, but an end date of 1/1/08 does not. I've tried numerous dates and the trouble appears to be with crossing year. Any thoughts? Thanks in advance. David Hi, What was your workflow triggered by and what would have been the start date being compared? Regards, Peter. -- Peter Clements Certified Microsoft Dynamics CRM Professional Capgemini UK "David J" wrote: > I have a simple manual work f...

Apply a simple formula to multiple rows?
Hello Esteemed Gurus :-) I have a formula for rows in the form =A10*DJ+E10*HJ+I10*LJ+M10*PJ+Q10*TJ The J is a variable that represents the row and there are over 100 rows, so it would be extremely boring to have to input this formula on every line. I'm exhausted after doing just 10 ;-) X(J)=A10*D(J)+E10*H(J)+I10*L(J)+M10*P(J)+Q10*T(J), J= Jstart, Jend ???, where X is the column of results??? Tried =SUM(A10*D+E10*H+I10*L+H10*P+Q10*T) on col X but no success. Any shortcuts? TIA rf Have a look at HELP index for INDIRECT -- Don Guillett SalesAid Software donaldb@281.com "...

how to adjust formula dependent on current month
Would like to create a formula that will adjust dependent on the current month. We are trying to get an average through the current month and do not want to have to change the formula each month to include the latest month. Thanks. - Ellen You could use an the offset command. This command allows you to offset (shift) the references in your formula. You will need a cell to tell Offset how far over to go Another idea is that if the numbers are blank until you have data, I think excel will ignore them for average purposes "Ellen" wrote: > Would like to create a ...

excel formula 12-28-09
I have rows of inventory numbers and I need to add a single number to the number already in the cell, make sence? I am adding additional inventory to the number in the cell and I don't want to have to add the numbers. It would be clear if you can post an example. -- Jacob "Shelley" wrote: > I have rows of inventory numbers and I need to add a single number to the > number already in the cell, make sence? I am adding additional inventory to > the number in the cell and I don't want to have to add the numbers. store 001 004 005 006 ...

Excel Formula?
How do I copy and paste a formula that automatically changes its references according to its position on the worksheet? Make sure that the formula does not have $ signs, e.g. change =$A$1*17 to =A17*17 -- HTH RP (remove nothere from the email address if mailing direct) "Loreilor" <Loreilor@discussions.microsoft.com> wrote in message news:1DDEF97F-11D3-4791-BE10-2FC0C4C41FF7@microsoft.com... > How do I copy and paste a formula that automatically changes its references > according to its position on the worksheet? Select the entire formula in the formula bar. R...

date formula #6
I have a spreadsheet that has the days of the month down column A. I' looking for a formula say in cell B1 that does the following, if today date < the first day of the month then return the first day of th month, then if today's date > the last day of the month then return th last day of the month. Thanks in advance B -- Message posted from http://www.ExcelForum.com Hi maybe I#m missing something but how can todays date be smaller than the first day of a month (it is at least the first day in a month)? -- Regards Frank Kabel Frankfurt, Germany > I have a spreadsheet tha...

Conditional formatting #125
I have a table with weeks one to 52 in the a column. Every week has new data enterred into it and I want to highlight the max and min values in different colours. I know that it is a conditional format. Here is the problem: 1. I may have the wrong formula. 2. I can't get the conditional formatting to work in cells that are currently blank. Ex. I have 13 of the 52 weeks enterred and when I enter new date in week 14 if its a min or max, the cell won't highlight. Please help, Thanks Assume values are in A1:AZ1 go to the conditional formatting for A1 only (highlight A1 and g...

Data Validation-Custom formula
I'm trying to set the validation to limit entries in cell K8 to the last day of the month e.g. 31/07/2004. I'm using the formula =eomonth(k8,0)=k8 which calc correctly in the sheet. * Entering with the leading = gives an error, but when I copy the formula in the Help example it allows the leading = sign? * Then when entering 31/7/2004 or any other eomonth date, validation returns an error. How do I fix this? Hi One way using Custom: =K8=DATE(YEAR(K8),MONTH(K8)+1,0) This works on the fact that Excel believes that the 0th of a month is the last day of the month before. -- Andy. &q...

help with formula and multiple associated data sets
I have a set of data that is linked to a certain Id number--in my table called "PIN". In the base data set, there are associated #s, such as total lot area, the zone, units, bldg sf etc however, each pin in this situation has maybe 2 different total lot areas and portions that obviously exceed each individual total lot area. Unfortunately, in the base table, I have also created percentages of each portion of the particular total lot area, and when adding the percentages, they exceed 100%. I want to look at the data of the whole pin--including multiple lot areas. i have ...

formula for inserting a letter
Excel 2003 In row 1, 2, 6 , 0, .... In row 2, I want A2, A6, A0, ... What's the formula for inserting a letter? Thanks +("A"&cellreference) PL wrote: formula for inserting a letter 30-Dec-09 Excel 2003 In row 1, 2, 6 , 0, .... In row 2, I want A2, A6, A0, ... What's the formula for inserting a letter? Thanks Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Developing Applications With Visual Studio.NET http://www.eggheadcafe.com/tutorials/aspnet/2e2c32ff-e76b-4f26-a...

Index formula
I am trying to build a index for lack of a better name. I would like to build a list several of the itmes "cells" at the beginning of a spreadsheet . When these items "cells" are double clicked, it would take you to the corresponding cell on the spreadsheet. The index cell would have the exact same information as the cell it would be looking for. I am continually adding rows to this spreadsheet so the row # would change as added. This may not be possible, but I have seen you guys do amazing things with excel on here. Thanks and keep up the great work!!!!!!!! John ...

array formula in vba
Hi! I have a worksheet in which I have a following array formula entered in Q1:Q1000 ={IF(MAX(IF($B$1:$B$1000=B2;$P$2:$P$1000))<30;0;1)} I would like to write a macro that would enter such formula into Q1:Q1000, the problem that I can't solve is the number of rows that is changing, sometimes I have 1000 rows, sometimes more. How to adjust the formula under vba to consider the number of rows? TIA Maciek Your formula looks kind of weird. You started in B1 in one portion and P2 in another. And you wanted to put the formula in Q1:Q###. I'm gonna guess that you really meant to st...

How do I reference a RANGE inside a Cell Formula?
Help ... I need to condense the following formula that's in multiple cells. While this example is for illustrative purposes ... my true cell formulas have reached the max character limit for a formula. So I'm desperate to find an alternate way to calculate the total number of specific shifts on a specific day of the month. I'm hoping someone can show me how to rewrite the formula using a RANGE that contains a member list consisting of both TEXT and NUMBERS: THIS is the existing Formula: {=SUM(IF($B$6:$B$75="F",IF(F$6:F$75=23,1,0)))+SUM(IF($B$6:$B$75="F",IF(F$6...

How to use same formula throughout the spreadsheet??
I would like to use the same formula throughout the spreadsheet, although the cell numbers and values will be different. For example =b1-SUM(c1:f1). How do I format it so this calculation takes place for each row of cells but with their respective cell numbers and values? Hi i'm assuming that you've got the formula say in A1 and you want it in A2, A3 ..... to do this, click on the cell with the formula move your mouse over the bottom right hand corner of the cell and you'll see a little + double click on the + this should copy the formula down as far as there is data in ...

excel formula question?help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
need help on getting this formula onto a excel page and then graphing the resul any help is mucho appreciated ti . this is for a file that has over 3000 names and i would like to be able to update this dail a. measure lwaccis as follows 1. if current close is greater than prior close,then lwaccdis=close-truelo 2.if current close is less than prior close,then lwaccdis=close-truehig 3.if current close is equal to prior close,then lwaccdis= b. calculation of true low 1. if pr...

canceling the formula aut-update feature with Arrays
I am using a spreadsheet to track a lot of Personnel across a lot of categories. The problem is when I move a person (row) to another sheet, Excel updates rages. This has caused problems with the summary page. This one of the formulas {=SUM(IF(MANIFEST!R1:R1000="O4",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O5",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O6",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O7",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O8...

Turning value of formula into actual cell content?
Hi. I've a sheet with a few thousand rows where in column B I need to add a prefix to every value. What I did was in column M fill the cells with the prefix, and made column N a copy of column B. Then in Column B I made the function: =M2&""&N2 and copied it all the way down (with incrementing numerals). That works great. But, I need to be able to copy-n-paste the new prefixed value of some of the cells in column B into another spreadsheet. But when I try, what I'm copying is the formula, not the resulting value. Is there a way to copy the values generated by the formu...

VLookup Formula's
On sheet 2 I have a table with formula's, these formula's are written in the Row style so that it will work on sheet 1. The lookup table has about 50 options. Sheet 1: Depending on the code in column C (example) I want excel to retrieve the formula from the table in sheet 2 (not the result) in column A. I suspect i would need VBA to achieve this and use vlookup for selecting correct formula. Does anybody have an example of such a VBA? In this case the trigger would be updating the value's in column C W If desired, send your file to my address below. I w...

Evaluate formula using VBA
How can I write a VBA code to evaluate this formula Y = alpha*EXP(-alpha*(X-U)-EXP(-alpha(X-U))) ...

all function formulas syntax
i want list on all excel 2003 functions, formulas and syntax with simple examples thank u There are some useful files on this page http://www.rondebruin.nl/id.htm -- Regards Ron de Bruin http://www.rondebruin.nl "azeem mansoori" <azeem mansoori@discussions.microsoft.com> wrote in message news:A0557C3A-AD82-4A7F-ADC1-D6255EFB7E6E@microsoft.com... >i want list on all excel 2003 functions, formulas and syntax with simple > examples > thank u ...

blank cells in a formula gives me an answer not acceptable to prin
need to insert certain statements to give a substitute answer such as NA until the second number is inserted into the blank cell Hi Al, How about =IF(ISERROR(F3/G3),"",F3/G3). If the calculation F3 divided by G3 returns an error, return "" to the cell containing the formula, if it doesn't return an error, perform the calculation and return the result to the cell containing the formula. -- Hope this helps, MarkN "AL VEGA" wrote: > need to insert certain statements to give a substitute answer such as NA > until the second number is inserted into...

Formula Encryption #2
Is it possible to encrypt or hide a formula in Excel and still input data into the worksheet? Thank you for your help, You could protest the worksheet so that the users could not visit that cell But the security is not that strong - we all have password breakers! best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ra" <Ra@discussions.microsoft.com> wrote in message news:1F3EEEE1-86E9-4394-8CC4-8D77ABAB47A2@microsoft.com... > Is it possible to encrypt or hide a formula in Excel and still input data > into the wo...

Formula view with no resulting value
I've not that experienced in XL-2003 and in older versions I can do this without any extra considerations When I'm entering a standard formula e.g. =today() excell don' other than that. I would have liked to have the number for the date today so that I can compare it with a date from another cell What do I do to make this happen Plleeeease Either you have View Formulas checked in Tools>Options>View or the cells are pre-formatted as text. Hit CRTL + `(above TAB key) to toggle Formula View on/off, if that is the case. If text cells, select the cells and Format as General t...

Formula for classification system #2
Hi all, Essentially what I am after is a formula, which I am assuming will have to include VLOOKUP at some point, that will allow me to classify data into a five character classification system. A spreadsheet with the data can be found here: http://spreadsheets.google.com/ccc?key=pIZmEkZZWJ0g3zkJbqI-EPA The first worksheet 'Class test' has my sample data, and the second worksheet 'Class VLOOKUP' is the actual data definitions. In column F- H on worksheet 2 are example codes derived from that actual data definitions. By using the data located in columns A-G on worksheet 1 I w...

Line graphs where data comes from formulas.
I am preparing a spreadsheet for use by a colleague and am trying to cut down on the work they will have to do. I have a spreadsheet with 52 tabs (representing each week of the year). Information is entered weekly regarding customer sales and formulas calculate costs of production and sales vs budget. Another tab is called ‘graph data’. Relevant information from the 52 tabs is collected on this sheet, some extra calculations are performed and then a series of line graphs are compiled. One row of excel, lets say row 6 contains information for the horizontal axis. So in cell B6 enter...