Install dates formating using conditional formating?

I have 2 cells (A1 and A2).  I would like to have the following take
place.   

1.  If I place a date that is either past or withinthe next 30 days
into A2 format the text as red.  

2.  If I place any date into cell A1, I need A2 to say N/A. 

I have been able to do #1 using conditional formating but am stuck on
#2.


Can anyone help?


Thanks


-- 
Jerry Eggleston
------------------------------------------------------------------------
Jerry Eggleston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9898
View this thread: http://www.excelforum.com/showthread.php?threadid=483552

0
11/9/2005 4:31:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
346 Views

Similar Articles

[PageSpeed] 52

Dates are just formatted numbers so there is no way of really testing for a
date except

=IF(ISNUMBER(A1),NA(),"Something else")

-- 

Regards,

Peo Sjoblom

"Jerry Eggleston"
<Jerry.Eggleston.1y8n4b_1131554106.6239@excelforum-nospam.com> wrote in
message news:Jerry.Eggleston.1y8n4b_1131554106.6239@excelforum-nospam.com...
>
> I have 2 cells (A1 and A2).  I would like to have the following take
> place.
>
> 1.  If I place a date that is either past or withinthe next 30 days
> into A2 format the text as red.
>
> 2.  If I place any date into cell A1, I need A2 to say N/A.
>
> I have been able to do #1 using conditional formating but am stuck on
> #2.
>
>
> Can anyone help?
>
>
> Thanks
>
>
> -- 
> Jerry Eggleston
> ------------------------------------------------------------------------
> Jerry Eggleston's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=9898
> View this thread: http://www.excelforum.com/showthread.php?threadid=483552
>


0
terre081 (3244)
11/9/2005 4:43:15 PM
Thanks for the help

I find that if I place the suggested formula in cell A2 it follows th
status of A1.  however I need to place a date into cell A2.  Should 
be putting the formula into the conditional formatting area

--
Jerry Egglesto
-----------------------------------------------------------------------
Jerry Eggleston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=989
View this thread: http://www.excelforum.com/showthread.php?threadid=48355

0
11/9/2005 5:49:50 PM
Reply:

Similar Artilces:

What is the basic format of a Access VBA module
Hello, I am almost completely new to Access VBA so pardon me if this question is asked before. Anyway, I came across somewhere that I have read before that before I exit an Access VBA module, I am supposed to clear the database or do something to free the database is it? May I know how it is done and why should I be doing that? Thanks! <azhu.uwaterloo@gmail.com> wrote in message news:1177681396.512608.194140@b40g2000prd.googlegroups.com... > Hello, > I am almost completely new to Access VBA so pardon me if this question > is asked before. > Anyway, I came across somewhere...

Outlook Client install hangs at MSDE sqlredis.exe
I see a number of people seem to have had the problem where an installation of the Sales for Outlook client just hangs about 30 seconds into the MSDE part of the install. It seems to get stuck with the sqlredis.exe process. Well on the strengh of what Microsoft support recommended, I suspected it might have something to do with my wireless/bluetooth connections and software to syncronise my cell phone with Outlook. So I killed off as many processes in my task manager I could find that seemed related to these, and the install worked! ...

CRM 3.0 Install Problems
We're trying to upgrade a dev instance of CRM 1.2 to 3.0. It gets partway through the installation before it fails, giving this error message: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. Execution of a full-text operation failed. The parameter is incorrect. Looking at the log, it looks like this is where it fails: 15:00:38| Info| Disable full-text indexing 15:00:44| Error| Install exception.System.Exception: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. ---> System.Data.SqlClient.SqlException: Execution of a full-text operation failed....

Conditional Formatting for a certain month
Having a bit of an issue at the moment, ive got the Cell for instance A1 Which contains 04-Jan-06 Basically Im after some conditional formatting to say if the month is January, then make the cell blue. Which will then lead to me making the other cells different colours depending on their month, but again, it has to be in this format (DD-MM-YY) Thanks for any help, muchly apreciated Assuming a properly formatted date, formula is =if(month(a1)=1) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Advo" <max_mischief@hotmail.com> wrote in message news:1158151582....

due date function
I try to make function for due payment. When I made copy file and then drag down the formula and it's succed. But for the cell that I not fill yet has due date also. How to make that cell became 0 (zero) if not fill the cell yet. example : A1 A2 19/8/05 =A1+30 - 30/01/00 - 30/01/00 Thanks Lando =IF(C5=""; "" ;C5+30) Explanation detail if ([logical test] c5 equals ""; then [result if true] ""; else [result if false] c5+30) Explanation brief if c5 eq...

Rich Text control formatted as bold??
I have a control field on my form that is setup as textformat = rich text. In the memo field on the form I need specific parts of the text to show up as bold. Upon form load I am populating the field with string data such as: Me.MyTextBox = "This is a test string generation." I need to set bold only one or two words of this string. The way I understood it was that if I was using Rich Text format it would convert the formatting to HTML style. But I don't see and havent found examples HTML formatting like [b] [/b] working in VBA. What is the correct way I can do t...

Date datatype in my schema
I have created a simple XML document, generated a schema from it (which Visual Studio makes everything a string element). I then went through and changed all fields that are dates to the Date data type. Now, when I use the datagrid to edit the data in the XML file and then switch back to the raw XML text date, the dates are formatted like this "1973-10-27T00:00:00.0000000-06:00" and not as I expected, which are supposed to be a simple date, such as "1973-10-27" as per the W3C spec. Also, when I validate the document, it fails on all date elements with this error: C:\Doc...

How can I autofill dates having a blank cell between each day?
Hi Dee, Enter a date in cell A1 (eg 5/13/08), enter the next day's date in cell A3 (eg 5/14/08). Select cells A1 trough to A4 (ie select two dates and two blank cells). See the little dot at the bottom right of the selection rectangle? Click on that (the cursor will change to a cross when you are over the right spot) and drag it down as far as you want. Ed Ferrero www.edferrero.com ...

conditional formating #4
I have an annual report spreadsheet comprised of 15 rows (1 - 15) and 5 columns (A - E). In columns "B" and "D" I add numbers from the current year, leaving future year cells blank. I highlight in yellow the blank future cells in columns "B" and "D" by a conditional format "formula is = ISBLANK(B9)". I leave past year cells in "B" and "D" with numbers but no highlight colors. I wish to highlight in green the current year numbers in columns "B" and "D", which will always be a cell above the firs...

Date Problems
Dear Microsoft: Since we all know that the cost of a 1st class stamp is going to go up, I thought I would plot the cost over time. http://www.nonprofitmailers.org/tools/ratehist.htm 1. I formatted a column as DATE, but the 3 dates prior to 1900 that I entered as x-x-xxxx were not recognized by Excel as dates. (??!!???!). When I reentered them as x/x/xxxx Excel recogized July 1, 1885, but not the other 2. (Huh?) 2. So then I charted the dates and the costs as a line chart, the costs are displayed ok. The dates are displayed on the x-axis, but the dates are equidistant. That's...

Open Spreadsheet--Formatting Changed to Date??
I opened my spreadsheet and some, but not all of the columns on every sheet in the workbook has changed to a date format. I opened other spreadsheets and there was no change. Can anyone explain this. It's a real pain to deal with. ...

formatting of txt file
Hi, Can anybody can help me in in formatting the below data for me The below data is coming in a txt file and i need the data in a excel file However when i put in excel i am not getting any structured form I need the Reference mentioned against the numeric value in the separate column as heading and the data below should come below the heading. I have tried, but i have to a lot of manual edit after formatting. 1.Emplyoee name bsdcbasdb hbvabfvas 2.Date 12-12-09 3.Address Dettails 12/2 main street City - bvauisdbc Pin code- 1100154 4. Department snbvsivnsvn oihbvis...

Can't set Date Properties.
I am working with data that has dates set as text. So in a query I am using a variable that sets the text to a date suing CDATE. At least I thought it did. I notice that in forms etc when I try to set the date property to long, medium etc, there is nothing to choose so presumably it is not a date after all! Any idea what I'm doing wrong please. Cheers. Roger --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.673 / Virus Database: 435 - Release Date: 01/05/2004 Sorted. I hadn't set the function as a date. Well I am n...

Microsoft Project Plan for Great Plains Installation
Does anyone have a microsoft project plan for Great Plains implementation? I will backup what Richard says. We have template built form the upgrade manual, then customize that for each client that we upgrade. My boss considers that proprietary information, so I can't share. Sorry... David "Richard L. Whaley" wrote: > In 37 years of implementing systems, I have found that any pre-written > implementation plan is incorrect. They need to be custom written based on > your firm's needs and requirements. > > -- > Richard L. Whaley > Author / Consult...

Add new record using first available non-numeric ID; update two ta
Access 2003, Windows XP Pro SP2 I have two tables, tblDed and tblVendor. tblDed contains two columns (DedID and Avail). The first column contains a list of all DedIDs allowed to be used. The second is a Yes/No choice indicating whether or not that DedID has been used already. tblVendor contains multiple columns with misc. data relevant to the Vendor. I want to create a form that will accomplish three things: 1. Use the next available unused DedID when a command button on a referring menu page (frmMenu, btnNew) is pressed. 2. Mark the now-used DedID in tblDed as "No" - no ...

Setting default date system to 1900 versus 1904
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I work in a large corporation that uses excel 100 times a day. My issue is that most of the files we work with are created on Win platform which defaults to the 1900 date system. When I create on the Mac, it defaults to the 1904 date system. I realize I can change the date format in preferences on an individual basis but is there anyway to set the default to 1900 versus me having to remember to go to preferences every time on each individual sheet? Thx On 3/4/09 9:22 AM, in article 59b6e6ef.-1@webcrossing.caR9absDaxw,...

RFID Use with Access
I am looking to find the steps necessary to make Access ready to interface RFID information captured from passive RFID devices. I would appreciate anyone in the group sharing this how-to with me. Zachry1 <Zachry1@discussions.microsoft.com> wrote: >I am looking to find the steps necessary to make Access ready to interface >RFID information captured from passive RFID devices. You should really be looking to the vendor of the RFID capture devices. They should have DLLs/OCXs which you can install and sample code. VB 6 code usually works just fine in Access. Tony -- Tony Toews...

OL2003 uses 100% of CPU resources after shutdown
If leaving my computer on over night with OL2003 with Business Contact Manager (BCM) running usually results in OL2003 stop responding but maintain 100% CPU usage. Even shutting down OL2003 w/BCM does not free up the CPU, which still shows OL2003 claiming 100% CPU usage. This also prevents my computer from going into power standby mode. How do I resolve this issue? ...

COUNTIF using formatting?
Here is a basic example of what I'm trying to do: I have a list of football teams. Each week, I format the football teams that won with red font. Is there a way to find a count of those teams formatting in red and display that number in my worksheet? I know an alternate way around this would be to have a win/loss column and do a COUNTIF based on the values there. But I'm wondering if there's a way to do it based on the formatting instead of the values. Nicolle K. wrote: > Here is a basic example of what I'm trying to do: > > I have a list of football team...

Dates in the Footer
I know how to insert a date in the footer of a document, but wondering if Word can automatically change the date of ONLY a page in a document that is updated...not the entire document. Would be very helpful to know when certain info is updated, but also to keep the last time other pages were updated. Thanks for any advice. JT On Sat, 24 Apr 2010 06:04:01 -0700, JT <JT@discussions.microsoft.com> wrote: >I know how to insert a date in the footer of a document, but wondering if >Word can automatically change the date of ONLY a page in a document that is >u...

Installing Office Assistant
Hello, I just installed Microsoft Office 2003 Student and Teacher Edition and I can't find the Office Assistant. Normally on other versions of Office I've used, in the Help Help pane there is the option to Show Office Assistant, but I do not have this option. I'd like to make use of Office Assistant, is there a way of installing it or is it not part of the Teachers' edition suite? Thanks in advance Hi Bobby! The only difference between the Student and Teacher (aka Academic) Edition is the terms of the licence. Office Assistant is an option. Close down your computer...

Stop Excel Changing Format
In Excel I want to just show a date as dd/mm in a cell. Every time I enter this Excel shows the number in dd/mm/yy format, even when I change the cell to use the General format. Any idea how I get Excel to show the number as I want? Format as dd/mm -- David Biddulph "Chris762" <Chris762@discussions.microsoft.com> wrote in message news:D028B2CD-5096-4EB5-8FFF-249AD6839774@microsoft.com... > In Excel I want to just show a date as dd/mm in a cell. Every time I enter > this Excel shows the number in dd/mm/yy format, even when I change the > cell > to...

Outlook 2007
Hi All I have a mail server configured on a IPv6 only machine. I would like to specify my POP3 account with these IPv6 Addresses as the Incoming mail server and SMTP server. For that in the Email Accounts i want to use IPv6 addresses. This is not working, i have also tried using the IPv6 address resolved name also, still it doesn't work. It works fine with IPv4 address and hostname. Can anyone help me solve this problem. Thanks Anilkumar Can you PING the IPv6 address? Can you use a HOSTS file to map the address to a name? <anilgkonline@gmail.com> wrote in message news:11781...

File Transfer using vb.net code
Hi, I would like to transfer/move a file from one folder to other folder in shareopint site. That needs to be done using vb.net. Could you please prove the code for that? Regards, Balu Submitted via EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips and Tricks # 16 http://www.eggheadcafe.com/tutorials/aspnet/960bc693-889f-46c1-9b0e-1b0a67d13ea5/dr-dotnetskys-cool-net.aspx So your asking someone on this list to do your work for you? For free? Sounds like you might want to get some consulting time. -- Daniel A. Galant Imagine what w...

Using Web Services To Add Leads to Marketing List
Is it possible to use web services to add leads/contacts to marketing list? I have a sales contact form that creates a lead, and I want to give those leads the option to signup for a mailing list we have. Does anyone know if this is possible? If so, know any examples? Thanks, Tareef Hi Tareef, The messages to handle list membership are AddMemberList and RemoveMemberList. They are described in the SDK documentation along with some sample code. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup D...