Conditional formating #7

Need help figuring out a formula please.

If "date entered" in cell exceedes todays date by 4 days, then format
the cell background "Red".

Thanks so much for any help you can give.


-- 
Unplugged
------------------------------------------------------------------------
Unplugged's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36751
View this thread: http://www.excelforum.com/showthread.php?threadid=564685

0
7/25/2006 1:07:38 PM
excel 39879 articles. 2 followers. Follow

9 Replies
452 Views

Similar Articles

[PageSpeed] 49

Use a formula of

=A2>=TODYA()+4

where A2 is assumed as the first cell with CF

"Unplugged" <Unplugged.2bi5mq_1153833004.6926@excelforum-nospam.com> wrote
in message news:Unplugged.2bi5mq_1153833004.6926@excelforum-nospam.com...
>
> Need help figuring out a formula please.
>
> If "date entered" in cell exceedes todays date by 4 days, then format
> the cell background "Red".
>
> Thanks so much for any help you can give.
>
>
> --
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36751
> View this thread: http://www.excelforum.com/showthread.php?threadid=564685
>


0
7/25/2006 1:14:58 PM
Click on the cell (let's call it A1)
Use Format | Conditional Formatting
Formula: $A$1-TODAY()>4
Then set colour as needed
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Unplugged" <Unplugged.2bi5mq_1153833004.6926@excelforum-nospam.com> wrote 
in message news:Unplugged.2bi5mq_1153833004.6926@excelforum-nospam.com...
>
> Need help figuring out a formula please.
>
> If "date entered" in cell exceedes todays date by 4 days, then format
> the cell background "Red".
>
> Thanks so much for any help you can give.
>
>
> -- 
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=36751
> View this thread: http://www.excelforum.com/showthread.php?threadid=564685
> 


0
bliengme5824 (3040)
7/25/2006 1:16:47 PM
Should be TODAY() of course


"Bob Phillips" <bob.ngs@nothere.com> wrote in message
news:uYRPVx#rGHA.4452@TK2MSFTNGP05.phx.gbl...
> Use a formula of
>
> =A2>=TODYA()+4
>
> where A2 is assumed as the first cell with CF
>
> "Unplugged" <Unplugged.2bi5mq_1153833004.6926@excelforum-nospam.com> wrote
> in message news:Unplugged.2bi5mq_1153833004.6926@excelforum-nospam.com...
> >
> > Need help figuring out a formula please.
> >
> > If "date entered" in cell exceedes todays date by 4 days, then format
> > the cell background "Red".
> >
> > Thanks so much for any help you can give.
> >
> >
> > --
> > Unplugged
> > ------------------------------------------------------------------------
> > Unplugged's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=36751
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=564685
> >
>
>


0
7/25/2006 1:21:58 PM
Wow!  Thanks so much for the quick reply.
Thought your formula works for the question I asked, I'm afraid I
incorrectly asked my question.
My apologies.  I will try again.

In Cell A1 I have entered a date. Eg.  01/01/06
What I want is for that cell to turn red when 4 days have passed the
entered date.

Looking forward to your reply.
Cheers


-- 
Unplugged
------------------------------------------------------------------------
Unplugged's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36751
View this thread: http://www.excelforum.com/showthread.php?threadid=564685

0
7/25/2006 2:13:13 PM
Exactly 4 days?

=A2=TODAY()-4

or more than 4 days?

=A2<=TODAY()-4

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Unplugged" <Unplugged.2bi8n3_1153836905.7232@excelforum-nospam.com> wrote
in message news:Unplugged.2bi8n3_1153836905.7232@excelforum-nospam.com...
>
> Wow!  Thanks so much for the quick reply.
> Thought your formula works for the question I asked, I'm afraid I
> incorrectly asked my question.
> My apologies.  I will try again.
>
> In Cell A1 I have entered a date. Eg.  01/01/06
> What I want is for that cell to turn red when 4 days have passed the
> entered date.
>
> Looking forward to your reply.
> Cheers
>
>
> -- 
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36751
> View this thread: http://www.excelforum.com/showthread.php?threadid=564685
>


0
bob.NGs1 (1661)
7/25/2006 2:54:30 PM
OK
I got it to work by using
=TODAY()>A1+4
The cell now turns 'red' when 4 days have passed, which is exactly what
I wanted.

Now the only problem is...
When I copy that conditional formating to another cell, which is
'empty', it gets filled in with 'red'.  
Is there a way to stop this from happening?

Thanks again.


-- 
Unplugged
------------------------------------------------------------------------
Unplugged's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36751
View this thread: http://www.excelforum.com/showthread.php?threadid=564685

0
7/25/2006 3:47:35 PM
You could use an "IF" statement in your conditional formula:

=IF(COUNT(A1)<>0,TODAY()>A1+4,0)

Then if there's something in the cell, it checks the date, otherwise, it 
assumes you don't want to conditionally format it.

Michael


"Unplugged" <Unplugged.2bid1g_1153842606.247@excelforum-nospam.com> wrote in 
message news:Unplugged.2bid1g_1153842606.247@excelforum-nospam.com...
>
> OK
> I got it to work by using
> =TODAY()>A1+4
> The cell now turns 'red' when 4 days have passed, which is exactly what
> I wanted.
>
> Now the only problem is...
> When I copy that conditional formating to another cell, which is
> 'empty', it gets filled in with 'red'.
> Is there a way to stop this from happening?
>
> Thanks again.
>
>
> -- 
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=36751
> View this thread: http://www.excelforum.com/showthread.php?threadid=564685
> 


0
mbehm (14)
7/25/2006 4:32:38 PM
=AND(A1<>"",TODAY()>A1+4)


-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Unplugged" <Unplugged.2bid1g_1153842606.247@excelforum-nospam.com> wrote in
message news:Unplugged.2bid1g_1153842606.247@excelforum-nospam.com...
>
> OK
> I got it to work by using
> =TODAY()>A1+4
> The cell now turns 'red' when 4 days have passed, which is exactly what
> I wanted.
>
> Now the only problem is...
> When I copy that conditional formating to another cell, which is
> 'empty', it gets filled in with 'red'.
> Is there a way to stop this from happening?
>
> Thanks again.
>
>
> -- 
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36751
> View this thread: http://www.excelforum.com/showthread.php?threadid=564685
>


0
bob.NGs1 (1661)
7/25/2006 5:01:54 PM
Thanks so much for all your help people.
It now works perfectly!

Really nice to know there are people out there who are so generous with
their knowledge and time.

Best wishes, and thanks again.


-- 
Unplugged
------------------------------------------------------------------------
Unplugged's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36751
View this thread: http://www.excelforum.com/showthread.php?threadid=564685

0
7/25/2006 5:16:25 PM
Reply:

Similar Artilces:

Gantt Chart in Excel. Do I need conditional formatting?
Hello all. I have created a Gantt chart that shows approx. 15 tasks in each project. I am using this to show percentage of completion for each task. It is working just fine and looks great but I need it to insert a date when a task is at 100% completion to show the client the date that particular task was completed. How would I do this without ruining my chart I already have working fine? I know this is probably easy to do but I cannot seem to keep the rest of the chart intact. Is conditional formatting the answer? Thanks! You could use Rob Bovey's XY Chart Labeler to do this: h...

multiple conditional formats
How can I set up a worksheet for more than 3 conditional formats You can't. You would have to use a VBA macro to apply the formatting, which would be static and not change with changes in the cell values -- you'd have to run the macro again. On Mon, 27 Sep 2004 18:57:03 -0700, "RC" <RC@discussions.microsoft.com> wrote: >How can I set up a worksheet for more than 3 conditional formats On Mon, 27 Sep 2004 18:57:03 -0700, "RC" <RC@discussions.microsoft.com> wrote: >How can I set up a worksheet for more than 3 conditional formats You can use ...

Percentage format lost
I have a form with an unbound text box whose row source type is this select query: SELECT CheckFinalPercentages.AllocType, CheckFinalPercentages.[Master-Sub], CheckFinalPercentages.SumOfPERCOUT AS Pct FROM CheckFinalPercentages ORDER BY CheckFinalPercentages.AllocType DESC , CheckFinalPercentages.[Master-Sub]; The query runs fine and returns the SumOfPERCOUT in percent format when I run the query...it used to return it in percent format also on the form, but I must have done something that made me lose it. Now, each time I open the form, the third column is returned in number format (1...

MSCRM Reports Export format
Does anyone know if there is a way to export reports as Rich Text Format (*.rtf)? or exporting to a CSV? We have a customer that still uses Wordperfect, and exporting to an Excel or Word Doc may not be an option. ...

How do I change the format of how my active cell in Excel is view.
I sometimes have trouble locating which cell is active in my Excel worksheet. I would like to be able to change it to something other than just a heavy black border. Here is one way that highlights the row and column of the activecell. Private Sub WorkSheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add T...

format changes on pivot table although preserve formatting is che.
Post the question the body of the message, subject lines get truncated -- Regards, Peo Sjoblom "Aannd" <Aannd@discussions.microsoft.com> wrote in message news:282F34F1-42CA-4F0A-8A62-A71C64E30741@microsoft.com... > ...

Conditional formatting of text
Greetings, all - I am creating a simple spreadsheet template to be distributed to users for data entry purposes. The first column contains the names of the desired data elements. The second column contains the definition for each data element. The definitions are formatted in red text. I intend for the user to remove the definition and replace it with the proper information for each element. For example: Data Element Definition 1.0 LastName The last name of the person to whom questions about the sample should be directed. The user would replace ...

Windows 7
How do I allow only certain computers on our network to access certain folders? I have my music folder that can be accessed by any pc on our network but wish to limit other folder to certain pc not all of them. Can anyone advise how I could do this ? Oh I have Windows 7 and the other pc's in the house are all Vista. Permissions in Windows are based on users, not computers. You have to create identical users with identical passwords on all the computers then give users permissions to shares based on who you want to access the shares. -- Kerry Brown MS-MVP - Wind...

Conditional Formatting
I have a row of cells that calculate the totals for their columns but I force to be blank via IF statements until certain conditions are met. Once these conditions are met, the total appears. I'd like to also highlight these total cells via conditional formatting. My conditional formatting input is simply "If Cell is Greater than 0, format". For some reason all the cells format as though they are all > 0 yet they don't display anything. Are there some internal rules that specifiy when a cell is really 0? Is there another way to do this that won't care what the for...

Conditional formula 05-12-10
trying to construct a formula for the following for the same cell: if a2 > 6, then e2 = 0 if a2 = 6, then e2 = 1 if a2 = 5, then e2 = 2 if a2 = 4, then e2 = 3 if a2 = 3, then e2 =5 this is for a golf scoring system. a2 values are hole scores and results are "handicap" point scores. Any help greatly appreciated Try this: =3DIF(A2>6,0,IF(A2<=3D3,5,7-A2)) Hope this helps. Pete On May 12, 4:14=A0pm, desmond1412 <desmond1...@discussions.microsoft.com> wrote: > trying to construct a formula for the following for the same cell: > if a2 > ...

Signature format changes in replies with Outlook 2007
Hi We are using Outlook 2007 and are finding that our signatures will sometimes change font and spacing in reply messages. Is there a way to fix this? Thanks ...

Excel document changes format
Hi, I have an Excel document that changes format unexpectedly. It seems to change the format of the document eventhough i saved it. I have to resize the windows of the bar graphs, etc. It just doesn't save it the way i want. I re-installed MSOffice and re-did the NT profile, but still persists. Any ideas? Thanks. Excel does seem to have problems if the screen zoom is anything othe than 100%. Usually changing to 100 and back resets he screen display -- Message posted from http://www.ExcelForum.com ...

The format of the e-mail address is incorrect
Good Morning, I'm getting the following message when attempting to send mail to anyone with an apostrophe in the domain name (ie. john.smith@wendy's.com) The following recipient(s) could not be reached: 'mark_eganhouse@wendy's.com' on 2/22/2005 2:13 PM The format of the e-mail address is incorrect. Check the address, look up the recipient in the Address Book, or contact the recipient directly to find out the correct address. Any help would greatly be apreciated get rid of the ' it is wendys.com "JZaragozaJr" <JZaragozaJr@discussi...

Publisher File formats
I need to send file in a tif or jpeg format but when I save the file I still get the pub extension after the .tif' Can I not save in anything but pub? Thanks Ray, It would help us to answer your question if you told us which version of Publisher you are using. -- JoAnn Two things are aesthetically perfect in the world - the clock and the cat. --Emile-August Chartier "Ray" <anonymous@discussions.microsoft.com> wrote in message news:006101c3dc62$7649ce40$a501280a@phx.gbl... > I need to send file in a tif or jpeg format but when I > save the f...

Formatting Cells #13
If you want something from cell (A) to appear in cell (B) and change when the contents of cell A changes, how do I do that? Acurran, =A1 ??? -- 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 2000 & 2003 ** remove news from my email address to reply by email ** "Acurran" <Acurran@discussions.microsoft.com> wrote in message news:E800CB99-E6BA-4DF3-BCA0-445A89B2E8A8@microsoft.com... > If you want something from cell (A) to appear i...

Conditional Format #23
I want to format the background of cell to a10 red if a10 is currently empty and the length of the string in a5 is 1. Can somone offer the correct syntax for the conditional format of a10 as everything I try fails. Thanks, Fred You need a condition of Formula is and a formula of =AND(A10="",LEN(A5)=1) -- HTH RP (remove nothere from the email address if mailing direct) "Fred" <Fred@discussions.microsoft.com> wrote in message news:2EBDDC73-FED7-451F-BCF3-A0E819888027@microsoft.com... > I want to format the background of cell to a10 red if a10 is currently...

Modify Protection Macro to allow formatting?
Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub How do I modify to allow users to format cells? Only been 4 minutes since your first post. Have a little patience. See reply at that post. Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 11:14:02 -0800, Visakha <Visakha@discussions.microsoft...

how to create a field based on many different conditions
Hi, I'm trying to create a field based on many different conditions in Make Table Query in Access 2003, e.g., if VAR1 and VAR2 meet certain condition, assign a value "A" to the new field "Category"; if VAR1 and VAR2 meet other condition, assign a value "B" to "Category" so on and so forth. I keep getting separate field for each condition, something like "Expr1023". Please help! Thank you! Since you haven't provided the expression or SQL statement, I will assume it is quite complex. I would remove the complexity from the query ...

Tasks - Monthly View
Outlook 2003, standalone computer. For some reason the rules don't function on my calendar view (month) of Tasks. Even the Default ones don't work. IE completed tasks should be strikethrough, and overdue ones in a different colour mine is supposed to be purple. But they all show as regular 8pt Tacoma. I did upgrade from Office XP a year or so ago. This is really frustrating because all my Tasks show up on the Calendar, even though I've completed some. Any suggestions? ...

conditional formatting colors
I need to somehow reflect drops in collections, but there is no set beginning amount to put in the formula bar. I need to highlight in bold or color decreases on a monthly basis and by quarter when drops of three consecutive months happen, I have tried everything, but nothing seems to be working. The spreadsheet is setup where as follows: a b c d e f g H name tume state desc rep month revenue collection it continues down with all of Jan. going down from column a4:a310 and then skips rows and begins Feb. stats a312:a624 and so on. I'...

Access 2007 and windows 7 12-25-09
Under windows xp and vista. I was using a batch file to compact a database, now under windows 7 it does not work I get an error. EXAMPLE: "c:\Consulting Tracking Program\Consulting Tracking.accdb" /compact Now I get a message box saying that was a problem sending the command to the program. Thanks for the help OD "OD" wrote: > Under windows xp and vista. I was using a batch file to compact a database, > now under windows 7 it does not work I get an error. > > EXAMPLE: > > "c:\Consulting Tracking Program\Consulting Tra...

[External Copy or SYLK] file format is not valid
I am moving to a new computer. I have WIN98SE on my old computer and WIN XP on the new one. I have Excel 97 and I am trying to transfer my data to the new computer. I get the above message when I try to open a file in XP. What is the problem? Thanks in advance, Bill. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Data format in pivot table
I am running a Pivot table on some swim data. Even though the data is formatted the same way "mm:ss.00", the fraction of the second is not showing up or is not part of the numbers in the Pivot table. Pivot table data Back 25 Breast 25 Fly 25 00:31.00 00:27.00 00:28.00 00:31.00 00:33.00 00:31.00 00:36.00 00:31.00 00:27.00 00:28.00 00:23.00 00:25.00 00:24.00 Data the Pivot table is based on 7 CMSA-SE 00:21.87 00:21.49 6 BMAC-SE 00:22.95 00:21.91 7 BMAC-SE 00:23.13 00:22.16 6 BMAC-SE 00:27.97 00:22.63 8 BMAC-SE 00:21.07 00:22.70 7 UN-SE 00:00.00 00:22.94 6 CMSA-SE 00:26.36 00...

Switchboard Format
How do you open switchboard pages (not the main page) in the design screen so you can edit the buttons, etc. Thanks in advance. -- S Afaik, you don't use design mode for the switchboard, other than making cosmetic changes. I believe all switchboard pages use the same form as a template, so each page will have the same "look-and-feel" as the others. The items placed on those pages are generated at runtime based on the table entries ("SwitchboardItems") that were created via the Switchboard Manager. Tools>DatabaseUtilities>SwitchboardManager. HTH, &qu...

Catalog & Excel formatting
I am using Excel as a data source for a catalog. One of the price fields in Excel is a 'currency' format and is the result of a formula. Publisher imports the field but only displays the figure as 45 instead of £45.00. Anyone know of a solution to this problem ? I can get around it by re-formatting the field as a 'number' format but as most catalogs will involve lists of products with prices I think the 'currency' format should work. Thanks. The best thing to do is do a SAVE AS in CSV (Comma Separated Values) and add a � to the figures and then copy and paste th...