#### Conditional Formatting #89

```I need to know how to make a block turn RED if the date exceeds a year. Thanks!
```
 0
ITGUY (22)
3/14/2005 1:35:02 PM
excel.misc 78881 articles. 5 followers.

3 Replies
500 Views

Similar Articles

[PageSpeed] 21

```Do you mean if the data exceeds one year from today? If
so, select the target cell, go to Format > Conditional
Formatting, choose Formula Is, and put:

=\$A\$1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

Change \$A\$1 to your actual cell reference.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I need to know how to make a block turn RED if the date
exceeds a year. Thanks!
>.
>
```
 0
jasonjmorin (551)
3/14/2005 2:20:46 PM
```Try this in - Conditional Format - Formula Is:

=DATE(YEAR(E1)+1,MONTH(E1),DAY(E1))<=TODAY()

If E1 was your cell to format.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"ITGuy" <ITGuy@discussions.microsoft.com> wrote in message
news:D76E136B-36DD-47DE-A43F-0E2B1A1BDB16@microsoft.com...
I need to know how to make a block turn RED if the date exceeds a year.
Thanks!

```
 0
ragdyer1 (4060)
3/14/2005 2:33:24 PM
```Thanks all,

I used =NOW()-365 and cell is less than or equal to that helped me achieve
what I wanted. Found it in another post.

"RagDyeR" wrote:

> Try this in - Conditional Format - Formula Is:
>
> =DATE(YEAR(E1)+1,MONTH(E1),DAY(E1))<=TODAY()
>
> If E1 was your cell to format.
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "ITGuy" <ITGuy@discussions.microsoft.com> wrote in message
> news:D76E136B-36DD-47DE-A43F-0E2B1A1BDB16@microsoft.com...
> I need to know how to make a block turn RED if the date exceeds a year.
> Thanks!
>
>
>
```
 0
ITGUY (22)
3/15/2005 10:49:03 AM

Similar Artilces:

Conditional counting
Hi, I am trying to do a count function where a specific condition is true. My example wants to count how many cells in c10:c30 contain numbers greater than zero where the person is a type "s" as defined in a10:a30. Can anyone assist? Thanks, Larry Hi Larry, =SUMPRODUCT((A10:A30="s")*(C10:C30>0)) -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Larry Whitman" <lwhitman@colubris.com> wrote in message news:003d01c35ab0\$597723f0\$a601280a@phx.gbl... > Hi, > > I am trying to do a count funct...

how do i write formula for if condition in excel?
??? =IF(A1=10,"yes","no") ?? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Anjali" <Anjali@discussions.microsoft.com> wrote in message news:307D83C3-34BD-4E0D-AF7C-4E10AA77BB1A@microsoft.com... > =IF(question_is_asked_in_a_detailed-manner,"quality, quick reply received","the best you get is a guess") -- HTH Bob Phillips (replace somewhere in email address with gm...

Date Formatting #6
Please can somebody help me. I need to disable date formatting in Excel 2003 & 2007. Excel's "helpfullness" is being VERY unhepfull at the moment. It is changing my style codes to dates, and if you format the cell to text then it applies some useless number that means nothing to me instead of what is supposed to be in that cell. I need to open already created csv files - so formatting the cells beforehand isnt an option. Please can somebody tell me how to disable date formatting entirely in Excel. The application dumps data to csv files. Users manipulate this data and wo...

Replace text format
I've got a customer that has a bunch of paragraphs within cells. She is using the Find/Replace option, to search for a particular word. She finds 5 instances of the word and wants to replace the formatting of this ONE word to standout against the rest of the cell's contents. When she uses the Replace and sets the format to Bold and Red, the entire cell's contents change to Bold red instead of changing just the single word. Is there any way to do it so that just the word and not the entire cell's contents change? Unfortunately, in this case it's not really an o...

Name Format is not Working
We have a brand new install of Microsoft CRM 3.0.5300. Under the Name Format under Home...Settings...Organization Settings...System Settings...General Tab it is set to "First Name Last Name", when I change it to "Last Name, First Name" and click OK twice, the setting does not stick. When I open up the System Settings again it states "First Name Last Name" under the Name Format and it does not appear that I can change this. Has anyone else seen this before? Jack Bender You have to do this things to make changes on organization settings: first open report...

Formatting Cells
Hello, I am trying to add a Custom format code that has the following format code: "0x"00000000 This works well if the numeric value only contains values from 0-9 (e.g., 1111, 12545, etc), but will fail if the input contains any character values (e.g., 'A', 'F', etc). So, if I entered FFFF0000, this would be displayed at 0xFFFF0000. I understand the reason why as Excel is treating it as a text string, but is there a way to get Excel to format the cells so I can enter characters of [0-9a-fA-F]??? Thanks, Mark on 11/7/2011, LordHog supposed : > Hello, >...

Average with Condition
I have a report sheet where Cell b10 represent the day of the month I need a formula that will look to a prior month sheet for the corresponding day as identified in B10 and average the sum sales to that point In the source sheet Column B (rows 9 thru 39) = day of month Column AE (row 9 thru 39) = sales -- ce In Excel 2007 there is a AVERAGEIF function (look it up in help) that should do the trick If you are in Excel 2003 this is not available but you can still achieve your result by doing a conditional sum and dividing by the number of days like this =SUMIF(S...

Account Format change
I need to add a new segment to my account framework and also increase a segment length for my existing accounts. For example: from 10-999, I need my account to read as 30-10-00999 where the first segment is the new segment and the last segment of 999 becomes 00999. Will Reformatter or the product from Microsoft do both of these things? Anyone has experience with this. I believe you need Reformatter from CRG. -- Charles Allen, MVP "Scott" wrote: > I need to add a new segment to my account framework and also increase a > segment length for my existing accounts. > ...

Conditional Formatting and selection
I have one column that has both name/address(in one row) and telephone numbers(in another row). I'd like to move the name/address info into it's own column. I've been able to do a conditional format to change the text info to a new color but I can't seem to mass select that info so I can move it all at once. Any ideas would be appreciated. if either the names or the telepphe numbers are all in even rows insert a helper column and enter =mod(row(),2) and copy down to the end of the data use autofilter on this column and select 0 select all and paste somewhere change the aut...

Formatting problem #3
Hi, In thspreadsheet, I'm able to format only one cell but not other cell besides this cell. Could anyone tell me how I could fix this problem. Thanks. Rajan -- Message posted from http://www.ExcelForum.com Hi Rajani More imformation required. Can you select other cells?, the worksheet may be protected. Bob C. >-----Original Message----- >Hi, >In thspreadsheet, I'm able to format only one cell but not other cells >besides this cell. Could anyone tell me how I could fix this problem. >Thanks. >Rajani > > >--- >Message posted from http://www.ExcelFor...

Format Function
Below is a snipplet of code that gets a recordset. I'm trying to output to a text file and keep my columns of data straight by using tabs. Because of the length of records in the field objRS!sShift, the vbTab code wasn't lining my column correctly, so I used the Format() function with the "@@@@@@" parameter and my data from the objRS!sShift now right-aligns. My new problem is I have a new field called "percentData" that is a percent that I need to display as 54.62% for example. Without the Format() function, the raw output of this field looks like 0.5462625 f...

Ok, here's a toughie. I have a list of numbers in column A and I would like in column B a smiliar number of asterisks like this: 4 **** 1 * 9 ********* Any ideas? Amy Give the REPT Repeat function a try, I think it will fulfill your needs nicely. "cvgairport" wrote: > Ok, here's a toughie. I have a list of numbers in column A and I would like > in column B a smiliar number of asterisks like this: > > 4 **** > 1 * > 9 ********* > > Any ideas? > > Amy =REPT("*",A1) -- David Biddulph "cvgairp...

Format Cell Dialogue Box
Hi there, Why don't you make the Format Cells dialogue box modeless. So that you do not have to keep opening and closing it. Jimbola There are no "you"'s here, if your post is referring to Microsoft developers. This is a peer to peer News Group manned(personned?) by volunteers and not by Microsoft employees. Your "modeless" idea is a good one, however................ There are many shortcut keys for formatting cells without opening the dialog box. Perhaps one or more of them would suffice. See answer wizard on "shortcut keys" Gord Dibben Excel ...

Outlook 2002 Not Displaying Messages In HTML Format
I can't for the life of me figure out why this is happening, but my Outlook 2002 is just displaying all messages in plain text format! Very strange. I only recently went up to 2002 from 2000. In 2000 it was fine. Windows XP Pro, SP2, all updates installed. Outlook 2002 v. (10.6515.6735) SP3 I currently have 2 add-ons installed: PocketKnife Peek Outlook Backup Any suggestions? PS - IE is not my default browser, Firefox is. In case that has anything to do with it. ---Atreju--- "Atreju" <someone@who.hates.junkmail> wrote in message news:kc31o1h5pbalnc92uunblsbpkoo7q...

"Automatic Formatting" pane in Outlook.
I can't work out how to get the "Automatic Formatting" pane in Outlook. Check here: Tools menu > Organize > Using Colors tab "Nick in NHS" <Nick in NHS@discussions.microsoft.com> wrote in message news:287E9578-94CA-4352-B373-C6A95710E66F@microsoft.com... >I can't work out how to get the "Automatic Formatting" pane in Outlook. ...

How to have Invoice Format be based on Customer Class ID?
Our company has two separate divisions which each invoice separately. We would like for each division to print its own invoice format. We will segregate customers by Class ID. Is there a way to have a given Customer Class ID default to a given Invoice Format? (Others have suggested VBA... can someone get us started on that code?) Thanks! Yes you could do this either using Dexterity or VBA. You would, of course, only be able to do this if printing single document because if you print a batch of invoices (by choosing a range of some sort) then it prints as one big report and so it woul...

Protection from format changes allowing form to be filled in
I have a questionnaire matrix to be filled in by others. I would like to protect the form from changes in format. However, others should be able to fill in the form by choosing from drop down list and/or writing in cells. The way I have been trying is: tools, protection, protect sheet, insert password, ok. Tried variations with contents, objects, scenarios boxes all ticked/partically ticked/blank. However, in all cases, the sheet becomes read only and does not allow to be filled in. Thank you for your help. Unprotect the worksheet (if you have it protected). Select the cells that the users ...

Format custom number: C-0000-L
Hi, I need to format a few cells in Excel to look like C-0000-L where "0000" represents four numbers and "L" represents a letter. I tried to do it in format - custom with C-0000-@ but it gives me an error message. I also tried to do it using the concatenate function but it doesn`t work. Can anyone please help me? Thanks! -- Sonia Custom format "C-"0000"-L" -- David Biddulph "Sonia" <Sonia@discussions.microsoft.com> wrote in message news:8EAB02FD-1AA5-4E80-880A-7C9A33A8FE01@microsoft.com... > Hi, I need to format...

International Date Format
I have a user who is on Windows 2000 and Office XP Professional. She is running some essential older software made in the mid to late 90's by a very microsoft- centric organization. She works in Ireland and uses d/m/yyyy type formats for her short and long dates in Regional Settings. When she exports a list from this older software that contains dates formatted d/m/yyyy (or some variation of this - dd/mm/yyyy) into Excel 2002, the dates look all right in the edit window at the top of the program but are misinterpreted within the worksheet as m/d/yyyy by Excel. Thus some appear...

Formatting changed when received by version 97
Hi, I have a problem with my Outlook XP. I am using Outlook XP with SP2 installed and using Word as my email editor. My problem is that when my emails are received by a 2000 or XP version of Outlook, all text is displayed correctly, but when a version 97 receives my mail then the font is changing mid sentence and extra blank lines are being added. The whole email look a complete mess. I know 97 only uses text and not HTML so something must be wrong with the way 97 removes the HTML tags. Has anyone had this problem or know of a fix? TIA Mark It is not so much that Outlook 97 does not ...

Count Unique with condition
Hello Everyone, I am looking for a formula to count unique occurances of column (A) where column (B) equals a certain value. Any help would be appreciated. I have the formula =SUMPRODUCT(('data'!A3:A6<>"")/COUNTIF('data'!A3:A6,'data'!A3:A6 &"")), which gives me a count of the unique values in 'data'!A, now I would like to break it down over a value in 'data'!B the result would look like this: smith, john, blue smith, john, red jones, james, yellow jones, james, green williams, john, orange williams, john, teal Uniq...

Format text box as Bulleted List as default
In AC2007 I would like to have a text box (rich text format) on my form automatically format the text that a user enters as a bulleted list when they begin typing. Currently the user needs to click the "Start a bulleted list" button on the ribbon before they begin typing. "Steve P" <Steve P@discussions.microsoft.com> wrote in message news:32E0219C-6D02-44AA-A292-38C2900F8F88@microsoft.com... > In AC2007 I would like to have a text box (rich text format) > on my form automatically format the text that a user enters as a > bulleted list when they...

generating a unique number in specific format
Hi, I currently have a stored procedure that generates a unique reference number. I did not use the SQL built in identity field to auto number because the number needed to be in a custom format. The format is CC (application identifier), a two digit year, a two digit month and a three digit unique number. For example CC0909001, for September 2009. The procedure runs a MAX query on the three digit unique number and adds 1 before generating the custom refernce and adding it to a primary key field. The procedure has worked fine but is now being used for multi user access a...

Email Attachments getting converted to Windows format in Outlook
When I open my IMAP mailbox using outlook it deletes and re-inserts any emails that have image attachments. The new email on the server is converted to a winmail.dat attachment format. This causes problems when I try to read the same file using my PDA since it can not read the winmail.dat format. How do I make outlook stop "auto" converting my attachments to winmail.dat. I have outlook configured to send email with plain text to prevent this problem when I send email I was shocked to learn that outlook was converting my email when I access the mailbox. Any help would be appreci...

Streaming CSV format to Excel 2000/2003 from web
Hi. Given the following data, "2002-10-10",6.67,,"3.32",Per Hansen,"Hans Persen",Hanseper Perhans,"Ting "" ling" "2002-11-11",6.77,,"4.32",Per Xander,Hans Persen,Hanseper Perhans "2002-12-12",6.87,,"5.32",Per Diller,Hans Persen,Hanseper Perhans Sent directly from a website with content type "application/csv", Excel 2000 starts up automaticaly opens it nicely in separate columns and proper types, while Excel 2003 does not. Anyone know if there's a way (content type and formatting) that w...