Conditional Formatting formula problem

Hi,

I want to conditionally format a cell (C1), but here's the problem.

I send off a form to someone and leave "Awaiting" in cell B1.  I pu
the date I sent off the form in cell A1.  If the date exceeds 21 day
from A1 then "Awaiting" in B1 is highlighted in bold red.  However, i
a form comes back I will type a date into B1, overwriting "Awaiting"
to which I want the font to normalise.

I hope that makes sense!!

If you know the conditions for this I would be most grateful!

Thanks,
Dan

--
Message posted from http://www.ExcelForum.com

0
8/10/2004 2:28:08 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
290 Views

Similar Articles

[PageSpeed] 47

Dan,

I think you mean "If the current date exceeds 21 days."  If so, try this.

Formula is:
=AND($A$2="Awaiting",A1<NOW()-21)
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Voodoodan >" <<Voodoodan.1as18u@excelforum-nospam.com> wrote in message
news:Voodoodan.1as18u@excelforum-nospam.com...
> Hi,
>
> I want to conditionally format a cell (C1), but here's the problem.
>
> I send off a form to someone and leave "Awaiting" in cell B1.  I put
> the date I sent off the form in cell A1.  If the date exceeds 21 days
> from A1 then "Awaiting" in B1 is highlighted in bold red.  However, if
> a form comes back I will type a date into B1, overwriting "Awaiting",
> to which I want the font to normalise.
>
> I hope that makes sense!!
>
> If you know the conditions for this I would be most grateful!
>
> Thanks,
> Dan.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere1083 (630)
8/10/2004 2:58:09 PM
One way:

CF1:   Formula is  =AND((TODAY()-A1)>21, B1="Awaiting")

In article <Voodoodan.1as18u@excelforum-nospam.com>,
 Voodoodan <<Voodoodan.1as18u@excelforum-nospam.com>> wrote:

> Hi,
> 
> I want to conditionally format a cell (C1), but here's the problem.
> 
> I send off a form to someone and leave "Awaiting" in cell B1.  I put
> the date I sent off the form in cell A1.  If the date exceeds 21 days
> from A1 then "Awaiting" in B1 is highlighted in bold red.  However, if
> a form comes back I will type a date into B1, overwriting "Awaiting",
> to which I want the font to normalise.
> 
> I hope that makes sense!!
> 
> If you know the conditions for this I would be most grateful!
0
jemcgimpsey (6723)
8/10/2004 3:04:32 PM
Dan,

Oops,

I used A2 when I shouldda used B1.
=AND($B$1="Awaiting",A1<NOW()-21)

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" <nowhere@nowhere.com> wrote in message
news:u1CrzpufEHA.2468@TK2MSFTNGP12.phx.gbl...
>
> Dan,
>
> I think you mean "If the current date exceeds 21 days."  If so, try this.
>
> Formula is:
> =AND($A$2="Awaiting",A1<NOW()-21)
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Voodoodan >" <<Voodoodan.1as18u@excelforum-nospam.com> wrote in message
> news:Voodoodan.1as18u@excelforum-nospam.com...
> > Hi,
> >
> > I want to conditionally format a cell (C1), but here's the problem.
> >
> > I send off a form to someone and leave "Awaiting" in cell B1.  I put
> > the date I sent off the form in cell A1.  If the date exceeds 21 days
> > from A1 then "Awaiting" in B1 is highlighted in bold red.  However, if
> > a form comes back I will type a date into B1, overwriting "Awaiting",
> > to which I want the font to normalise.
> >
> > I hope that makes sense!!
> >
> > If you know the conditions for this I would be most grateful!
> >
> > Thanks,
> > Dan.
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
>
>


0
nowhere1083 (630)
8/10/2004 3:51:26 PM
I used Earl's version in the end, but thanks for all the response
received for this.

It works perfectly.

Thanks very much.
Dan

--
Message posted from http://www.ExcelForum.com

0
8/11/2004 11:12:20 AM
Reply:

Similar Artilces:

Paragraph formating jumps to defaults.
Hi, We are using Office/Outlook 2007 We have an email signature script that is run when users login, it´s working ok except for two things; If the default paragraph settings in Word 2007 for a user matches those in the signature script, the signature will use the "Base defaults" of Word 2007, that is SpaceAfter = 10 and LineSpacing = "Multiple" with a value of 1,15. So if I change the script to use SpaceAfter=0 and the user has set his/her Word 2007 to use SpaceAfter=0, the result will be SpaceAfter=10. If the script is set to SpaceAfter=0 and the us...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

Where is the lasso feature in 2008? (was in formatting palette in 2004)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, <br><br>In Excel 2004 in the formatting palette in image, there are different selection marquees and lasso's. Where are these features in 2008 Excel? I can't find them anywhere. <br><br>thnx I just found my answer, the &quot;genius's&quot; at Microsoft / MAC Office decided to kill off all these editing features... <br><br>Over $200 to upgrade to a product with LESS features... I don't think so... unbelievable, I will continue to use 2004 because 2008...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

formatting auto replies
Does anyone know of a method that allows outlook 2k2 to format automatic replies regardless of the format of the original message? I setup this automatic response rule that replies with a template, but problem is, when replying to http clients (hotmail or yahoo), the message at the receiver's end has words totally misplaced, all over the page. Any suggestions would be appreciated. ...

Problems migrating users from E2K to E2K03
Having problems when I try to move a mailbox over to Exchange 2003 server from a Exchange 2000 SP3 everything seems to go fine other than the fact that now when sending e-mail I receive "The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address." I have no problems when I create a mailbox for a user on the E2k03 box, but when it comes up to moving a maibox it does not seem to go as smooth. Stumped Al Hi Al, It sounds like some of the attributes for the user...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041 View this thread: http://www.excelforum.com/showthread.php?th...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Varying format
Is there any way to format a cell differently depending on the number of decimal places typed in? What I need is a minimum of 2dp but 3 if the typed entry matches. eg 1.2 appears as 1.20 and 1.234 appears as typed. TIA Ian Hi! Try formatting the cell with a custom number format such as 0.00## Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforum.com/showthread.php?threadid=27494 "AlfD" <AlfD.1f5q6o@excelforum-nospam.c...

Setting appointment start & end time increments format
In Outlook 2002, I can easily set the Day View to display the time increments in 15 minute blocks, but on the Appointments form, the Start and End Times available on the drop-down menus, respectively, are in 30 minute increment blocks only. How can I set the format for the appointment page to have the Start and End Times for appointments have increment time blocks of 15 minutes? Thanks! My direct email address is: solutioncounselor@comcast.net . If you set your appointments in 15 minute blocks Outlook will start to offer that. It takes a few uses to work. -- Nikki Peterson [MVP ...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...

difficult format
I want to format a code like this: ##.0001.## so then i push it down and gives me 0002 and so on, the other ## r numbers. The problem is that when i do that, it sums in the last two numbers instead of what i want, so i ask if it is possible to format like this: (text)##.(number)0001.(text)## so it guives me the sum of number then do what i want. if it is possible how ca i do that? Thanks You can try this, starting in row 1: ="##."&TEXT(ROW(),"0000")&".##" You'll have to adjust the "Row()" number when starting in any other row. For example,...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...