Conditional Formatting Dates 02-18-10

I am attempting to highlight past due dates (I am horrible with the formula's 
and can not seem to figure out how to NOT highlight if there is no 
information in the cell's)

A1=Planned date and A2=Completed date

Conditions I am trying to figure out
1. Would like A1 to highlight RED if less than Now and A1 is NOT BLANK or A2 
is BLANK
2. Would like A2 to highlight green if filled out and less than or equal to A1

Thanks anyone that can help!!!
Katie


0
Utf
2/18/2010 5:56:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1355 Views

Similar Articles

[PageSpeed] 22

CF formula for A1:
=OR(ISBLANK(A2),AND(A1<TODAY(),ISNUMBER(A1)))

CF formula for A2:
=AND(ISNUMBER(A2),A2<=A1)
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Katie Schertzing" wrote:

> I am attempting to highlight past due dates (I am horrible with the formula's 
> and can not seem to figure out how to NOT highlight if there is no 
> information in the cell's)
> 
> A1=Planned date and A2=Completed date
> 
> Conditions I am trying to figure out
> 1. Would like A1 to highlight RED if less than Now and A1 is NOT BLANK or A2 
> is BLANK
> 2. Would like A2 to highlight green if filled out and less than or equal to A1
> 
> Thanks anyone that can help!!!
> Katie
> 
> 
0
Utf
2/18/2010 6:13:02 PM
Reply:

Similar Artilces:

FRx cell formatting
It would be a nice feature to have specific cell formatting in FRx. For example having the % format on several rows and then within the same report changing that to number formatting. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businessso...

Excel 2007 displayed dates
When I enter dates in the Uk english format (dd/mm/yyyyy) in Excel 2007, even though they are formatted to display dd/mm/yyyy, they are dispayed in the US english format (mm/dd/yyyy). The formula bar shows that the cell contains a date in the UK format as entered. The language setting in Excel Options is UK. I wrote a macro containing an InputBox requesting a date, having previously defined the variable for this input as Date. The input is entered in the UK format but when assigning the value of this variable to a cell, it is displayed in US format. Not only is it displayed cont...

Cell formatting #4
For print formatting purposes I need to duplicate a keyed in numeric value from one cell (say A4) into another cell (say Z4). In many cases the original cell (A4) will be blank. How do I make the duplicate cell (Z4) remain blank also? Right now I end up with the original cell (A4) as blank and the duplicate cell (Z4) as a 0 value. If I do put an entry into the original cell, it will be numeric (say 1.3). The duplicate cell will have the same number of significant digits as the original cell. On Sat, 27 Dec 2008 17:04:00 -0800, FatBob <FatBob@discussions.microsoft.com> wrote: ...

Compare string, wildcard, text/cell formatting
Hi. 1) I would like to compare the following: If the string satisfies the following conditions, it returns true: - start with some letters (eg for "no-", strings like "no", "not", "none" satisfies so) - have some letters in the mid (eg for "-ppl-", strings like "apple" satisfies so) - end with some letters 2) It would be great if wildcards (eg ?, *, + etc.) can be used in the above functions. If possible, how? 3) How to set it that if the string satisfies condtion stated in (1), some ""text"" AND ""cel...

excel vba
I have a workbook set up and i'm having a tough time with this protection stuff. I have tried different .unprotect methods but it doesn't work. I have a print button with coded conditions such as if cell a1 = "this" then certain cells get the borders formatted to be white, etc. then it prints one page, changes the format back and then prints 2 pages like that and saves the page. Anyways, bottom line is that there are format changes which are done before and after printing. My code is set up so that as soon as the person goes into the main page, it becomes protected in order t...

Show task due dates in my calendar?
Hi everyone, I have Outlook 2000. When I create a task, I try to always give it a due date. How can I have that due date automatically show up in the Calendar? I don't like switching back and forth between Mail, Calendar and Tasks. It seems like Tasks and Calendar could be more tightly integrated. Thank you in advance, -= Stefan Lasiewski ...

hello 05-13-10
watch oot. there da polis ...

Excel Internal Format
I would like to get the contents of my workbook into an external file. Ideally, the file would have one line for each cell in the notebook. The first field would be the fully qualified name of the cell and the second field would be the contents of the cell. If the cell contains a formula, the field would be the formula or it would be the value if the cell contained a value. I don't care about the formatting (LEFT,etc). Does anyone know how to do this? Jochen Haber ...

10 random numbers from 0 to 20
Hello, how can I write a method that returns me 10 random numbers from 0 to 20 (included), without repetitions? Thanks a lot. Luigi On 15-06-2010 12:10, Luigi wrote: > Hello, > how can I write a method that returns me 10 random numbers from 0 to 20= > (included), without repetitions? If you don't want repetitions they are not real random numbers. You could get the number '15' 10 times in a row, it is possible, but=20 highly unlikely. --=20 Med venlig hilsen / Best regards S=C3=B8ren Reinke, IDCS #505926, TDI Trimix www.Dykkeren.dk Dive bl...

Autoincrementing dates
I have a column formatted as "Date" with format March-01, etc. If I enter Jan-08 in A1 how do I get Feb=08 to appear in A2, etc ? I tried A1+1 but the syntax is not understood by Excel 2003. Many thanks. In A1 enter Jan-08 and drag till u want it will show only Jan-08, at the end of the cursor u will autofill option click for more option and then choose fill months u will get the result as u need...... On Apr 6, 10:16=A0am, "Andrew Chalk" <ach...@magnacartasoftware.com> wrote: > I have a column formatted as "Date" with format March-01, etc. &...

Expiration dates on vouchers
Is there any way to specify an expiration date on a merchandise voucher? We want our gift certificates and refund vouchers to expire after one year from the date of purchase. After that time, any unused balance on expired vouchers will be written off. Balances on merchanidse vouchers are considered a liability in our accounting system, and we don't want to carry that liability beyond one year from the time the voucher is issued. Bill Yater The Worth Collection byater@worthltd.com Bill, Not at this time, but you can run a Voucher report to see your outstanding, SO Manager | Repor...

help with Conditional formatting pairs of cells
I have a spreadsheet which is laid out as a burial ground map showing who is buried where and whether a headstone is in place. 2 cells represent one grave as there may be 2 people buried there. The format has 4 conditions: Empty used used+memorial reserved. The sheet has 10 columns of around 20 pairs of cells. Conditional format over the whole range works fine when the pair of cells for a grave are marked empty or where both cells are used (for 2 people in a grave). My problem is finding an easy way to conditionally format the second cell of each pair to match the ...

Date Invoice Paid Off
Customer wanted to show the Date Invoice Paid Off (DINVPDOF) field in his Sales Transaction History report. The steps: 1. I created a SOP Invoice with one Line Item. 2. I input an Amount Remaining equal to the total Document amount. 3. I posted the SOP Invoice. 4. I went to SQL Query Analyzer and have seen that the DINVPDOF in RM20101 didn't get updated with the correct date. There is no DINVPDOF field in Sales Order Processing (only in Receivables Management). Customer is hoping there is a possibility for the database to either have a DINVPDOF in SOP or for the DINVPDOF field in R...

Dates in chart
I have a chart that has data for Monday-Friday only of each week for 12 weeks. The dates are based upon a formula, so alternate every week showing a rolling 12 week chart. Unfortunately, the chart is showing 5 days with 2 days for the weekend being blank as there is never data at the weekends and so far attempts to rid the weekends, which are not in the original data at all, have proved futile. Can anyone suggest how to rid Saturday and Sunday from the chart? All help appreciated. Ellie Hi, You need to set the axis to be category rather than date. Right click chart and pick Chart O...

Formatting unlocked cells
How to do formatting of unlocked cells? I have locked certain cells and wish the user to have freedom o formatting other (unlocked) cells. But once the sheet is protected formatting etc. are not possible even for unlocked cells. I am using Excel2000 and under protection password dialogue, the chec boxes are as 'Contents', 'Objects' & 'Scenarios'. How to solve the problem -- Message posted from http://www.ExcelForum.com Hi Bhuktar You can't, this behaviour is by design in Excel. HTH Cordially Pascal "Bhuktar S >" <<Bhuktar.S.18lx28@excelfo...

help #18
I have a column in an excel sheet (max 65000 rows) which contains text. I want to strip off the first word of the text string and place this into a different column leaving the rest(minus this one word) in the original column cell. in toal i have 85000 records and doing this by hand will take for ever. Once this is done I will transfer all records to an access database. can some one please give me a guide on how to go about doing this In an adjacent column =RIGHT(A1,LEN(A1)-FIND(" ",A1)) and copy down. -- HTH RP (remove nothere from the email address if mailing direct...

FORMATTING - hard questions
I have a cell which has plain text i.e. "Hello World" but between Hello and World I press Alt-Enter to place the word "World" on the second line so that the column width doesn't have to be long. But when I reference this cell from another one, Excel not only shows a weird ASCII character in between the words but also does not show the two words on separate lines as in the original cell. Is thre a way around this? TRIM and CLEAN did not do anything. Also, I have a function that compares two cells =IF(A1=A2,"YES","NO") and I would like to do a cond...

Formatting XmlSerializer Output
I'm using classes generated from xsd.exe. I am serializing them with XmlTextWriter, and everything works fine. Our customer, though, has a silly requirement that dollar amounts (in the .xsd as decimals) be formatted to always include cents (regardless if there are cents or not). I'm interested in using the classes to only output data (i.e. I'm not parsing incoming data) I've tried setting up the following construct to format the output of each of the affected decimal fields, but I don't get anything written out to the .xml file. What am I missing - and is there a bette...

Chronological Date Orders
Hi I have a worksheet column (L6:L34) into which users are asked to enter dates that payments are made. The columns are formatted as 'Date' and Data Validation is set to ensure a date is entered. A blank entry is allowed. Initially all the cells are blank and the user is expected to populate the cells, starting with the earliest date in L6 and moving sequentially down the column. Of course, there's no guarantee it will happen that way. It doesn't matter if a cell is skipped (left blank), but I would like to ensure that any dates that are entered, are entered in chron...

Display Cond. Format Formulas
Is there a way to display a cell's conditional format formula w/o actually going through the menu to look at it? I need a way to quickly tell what formula a cell is using for conditional formatting. It would be nice if you could hover your mouse over the cell and the formula would be diplayed (similar to a comment), but I don't know if this is available. Thanks. >>It would be nice...but I don't know if this is available It's not. About all you can do is speed up the process of opening the dialog slightly with a macro. You could attach this macro to a toolbutto...

Conditonal format based on another cell value
Hi, I'd like to format a row, or part of a row, based on the value in one cell of the row... The example I'm working on is a GPA calculator for my wife, we want to be able to add classes for future semesters, but format them differently based on the clomun "Semester" which has a number value so that the entire row is, say, bold fo past, italicised for future, plain for current. Is this possible? Thanks, Camwyn Camwyn yes, you can do this quite easily: in the conditional formatting dialogue, set the first condition to be: Formula is =$A2="past"; set the form...

which date in cells add up to a certain cell
Hi, I would like to know if there is a way to find out which rows add up to a certain dollar amount. For example, I have 134 rows of different dollar amounts and would like know which rows would add up to $11,464.95. I would like to know exactly which row A3, A9, A100, A99, etc. Is this possible? Thanks in advance, Kerri Hi, look here http://www.tushar-mehta.com/excel/templates/match_values/index.html "Kerri" wrote: > Hi, I would like to know if there is a way to find out which rows add up to > a certain dollar amount. For example, I have 1...

date #12
opening a CSV file into Excel. I need to convert one column full of dates. I tried the text to columns function.. however it only changes the date with two digits in day ( eg. 13022005 is converted to 13/02/2005) but the dates with single digit day ( eg. 9022005 are not converted to 9/2/2005) any help pls May not be the most efficient wany but I would use... =DATE(RIGHT(A1,4),IF(LEN(A1)=8,MID(A1,3,2),MID(A1,2,2)),IF(LEN(A1)=8,LEFT(A1,2),LEFT(A1,1))) (assuming your date string is in cell A1) "flow23" wrote: > opening a CSV file into Excel. > > I need to convert on...

Conditional Formatting (on OpenForm?
Hi, I have a form with datasheetview. I want to add conditional formatting to some textboxes. I have to do this via code, because it can be variable. (The expression compares the vendor-price with the client-price.) I add the conditional formatting on OnOpen-event. The problem is that the fields have a NULL-value at that time, I guess, because it crashes on the expression. What am I doing wrong? Via the dialogboxes this works, but via code it crashes. (Type mismatch error) ---- Code: ----- Me.TCP_VENDOR.FormatConditions.Delete Set objFrc = Me.TCP_VENDOR.FormatConditions.A...

File Formats
Will my *.mny file from Money 2001 work with Money 2004? Provided 2001 and 2004 are from the same region. i.e. both US or both UK. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "tcolby" <tcolbymn@yahoo.com> wrote in message news:3f9101c3764f$fb2adc90$a301280a@phx.gbl... > Will my *.mny file from Money 2001 work with Money 2004? To be precise, Money 2004 will upgrade your Money 2001-compatible file to one that is Money 2004-compatible (assumin...