Date format of 0 and if statements

I have an is statment as such:

=IF('Raw Data'!A5=0,,'Raw Data'!A5)

This issue I have is when I format fields as "date" and the
reference cell = 0 excel formates the cell as 01/00/00.

I have also used the formula:
=IF('Raw Data'!A5=0,"",'Raw Data'!A5)

The issue I have is I want to create graphs of this data in
a  dynamic fashion, but when the data range of the graph
encounters either "" or 1/00/00 as a date, it plots it as a
data point.  I need a way to make my IF statment to produce
a BLANK or EMPTY cell.  Is there a key word in excel for this?

0
1/8/2004 3:55:41 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
518 Views

Similar Articles

[PageSpeed] 52

You can use NA() to give a point that won't be plotted on a graph.  If using a
line graph though this will not work with a stacked graph, eg:-

=IF('Raw Data'!A5=0,NA(),'Raw Data'!A5)

Downside is that if you are summing these ranges, then the error will kill that,
so you need to account for it in any calculations

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"brent" <bwalker@orbitz.com> wrote in message
news:0c1801c3d5ff$de4582a0$a601280a@phx.gbl...
> I have an is statment as such:
>
> =IF('Raw Data'!A5=0,,'Raw Data'!A5)
>
> This issue I have is when I format fields as "date" and the
> reference cell = 0 excel formates the cell as 01/00/00.
>
> I have also used the formula:
> =IF('Raw Data'!A5=0,"",'Raw Data'!A5)
>
> The issue I have is I want to create graphs of this data in
> a  dynamic fashion, but when the data range of the graph
> encounters either "" or 1/00/00 as a date, it plots it as a
> data point.  I need a way to make my IF statment to produce
> a BLANK or EMPTY cell.  Is there a key word in excel for this?
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004


0
ken.wright (2489)
1/8/2004 4:41:43 PM
A cell will never be "empty" when it contains a formula, 
but you can use NA() to overcome your graphing issue:

=IF('Raw Data'!A5=0,NA(),'Raw Data'!A5)

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have an is statment as such:
>
>=IF('Raw Data'!A5=0,,'Raw Data'!A5)
>
>This issue I have is when I format fields as "date" and 
the
>reference cell = 0 excel formates the cell as 01/00/00.
>
>I have also used the formula:
>=IF('Raw Data'!A5=0,"",'Raw Data'!A5)
>
>The issue I have is I want to create graphs of this data 
in
>a  dynamic fashion, but when the data range of the graph
>encounters either "" or 1/00/00 as a date, it plots it as 
a
>data point.  I need a way to make my IF statment to 
produce
>a BLANK or EMPTY cell.  Is there a key word in excel for 
this?
>
>.
>
0
jason.morin (561)
1/8/2004 4:43:29 PM
Brent,
If you use Jason's suggestion to fix your graphing problem you will have #N/A showing up in your table.  If you want to keep your table clean you can set up a conditional format to change the font to white when the cell contains #N/A.  This way the NA error will prevent the cell from graphing, and the white font will prevent it from showing in your table.

Good Luck,
Mark Graesser
mark_graesser@yahoo.com
     
     ----- Jason Morin wrote: -----
     
     A cell will never be "empty" when it contains a formula, 
     but you can use NA() to overcome your graphing issue:
     
     =IF('Raw Data'!A5=0,NA(),'Raw Data'!A5)
     
     HTH
     Jason
     Atlanta, GA
     
     >-----Original Message-----
     >I have an is statment as such:
     >>=IF('Raw Data'!A5=0,,'Raw Data'!A5)
     >>This issue I have is when I format fields as "date" and 
     the
     >reference cell = 0 excel formates the cell as 01/00/00.
     >>I have also used the formula:
     >=IF('Raw Data'!A5=0,"",'Raw Data'!A5)
     >>The issue I have is I want to create graphs of this data 
     in
     >a  dynamic fashion, but when the data range of the graph
     >encounters either "" or 1/00/00 as a date, it plots it as 
     a
     >data point.  I need a way to make my IF statment to 
     produce
     >a BLANK or EMPTY cell.  Is there a key word in excel for 
     this?
     >>.
     >
0
anonymous (74722)
1/8/2004 4:56:23 PM
Reply:

Similar Artilces:

Check Box Formatting ??
Hello Everyone. Its me again. Please can you help me with the following. I have a workbook with 13 sheets in it. Name of first sheet is Holidays. The others are named Apr, May, ......... Mar. I would like to place a check box (if this is the right thing to use)on each monthly sheet that would send the value of cell Mar!I48 to Holidays!D13 and Mar!I50 to Holidays!D17 in one operation. If the check box is ticked the values of I48 and I50 should be transfered. If not ticked the value should default to zero. This is to be repeated on Apr, May etc obvioulsy to different cells on the the ...

RMS should have a search strategy for "date account opened"
EOM ---------------- 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/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=0b38650c-32cb-40cb-8b52-e4aab95e11f9&dg=microsoft.public.pos you can do this with reports. "samax" <s...

Convert Word 6.0 to Word 2003 programatically
With the latest security update from MS Security Existing word templates documents could not be loaded and they started throwing exceptions each time we load. We would like to convert the existing Word 6.0 to Word 2003 programmatically or by using a tool. How do we convert using .NET? Or any existing MS Tool Thanks for the help It sounds like your problem maybe with the KB973904 security update. Try removing it. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, ...

Grouping mails by their date received
Hi there, I would like to group my mails on "Received" date so as to figure out my activities on a particular date. Microsoft Outlook 2K SR1 allows its users to format the columns the way they want and group mails by those columns (except columns with formula) When I customize the "Received" column with "mm/dd/yy" format and group by it, Outlook falls back to the original format ignoring my customization. This results in a useless view in which Outlook groups the mails upto minutes accuracy and ends up puting one mail on each group and creating as many groups ...

DPM 2010 release dates
Hi, I was wondering if anyone could define what the current projected release dates for DPM 2010 are? I'm specifically looking for RC and RTM dates. I've heard that the RTM is now expected to ship in early summer and wanted to confirm. Here is an old information I gathered before. I am not sure it is still accurate. So lets wait for the DPM team's answer: http://santhoshsivarajan.blogspot.com/2009/12/dpm-2010-timeline.html -- Santhosh Sivarajan | MCTS, MCSE (W2K3/W2K/NT4), MCSA (W2K3/W2K/MSG), CCNA Houston, TX http://blogs.sivarajan.com/ http://publications...

Import Mail from Netscape 7.0
I work for a company that uses Netscape 7.0 for our email. We are looking into converting to Outlook 2003, but I cannot find a way to import messages from Netscape 7.0 into Outlook or Outlook Express. All the articles I read are about importing 4.7 and below. Can anyone help me? Thanks! I believe that is the tool for it; downgrade to Netscape 4.7 first and import the messages into OE and then export them to Outlook. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Jo...

how do I make Excel default to General number format?
I have installed Excel 97 but the General cell format insists on formatting as the number format with two decimal places. Is the number you enter already a two decimal number? General removes any type of formating, and represents the contens as WhatYouSeeIsWhatYouGet "rjmo" wrote: > I have installed Excel 97 but the General cell format insists on formatting > as the number format with two decimal places. You may have accidentally changed the "Normal" Style. Go to Format | Style... and make sure "General" is applied to the "Normal" style. ...

Last Meeting Date
I am looking for a way to populate the last meeting date into a custom field of a contact entity. I did not see how to do this with the MS Workflow. You can't do that from workflow unless you going to write a workflow assemly to pull the information through web service or filtered views. Are you familiar with the CRM 3.0 SDK? Darren Liu Crowe Chizek and Company http://www.crowecrm.com On Mar 30, 10:10 am, "tim" <computerti...@gmail.com> wrote: > I am looking for a way to populate the last meeting date into a custom > field of a contact entity. I did not see how...

Page format changes when document is shared
I have an Excel document that was created by someone else. I saved it and shared it. When printing, the page format is different for others than it is for me. Also, the page format changes for those users if I turn the "save" function off. How can I get the page format to be the same for each user. Everyone is using the same version of Excel. ...

Does CRM 4.0 work well with Office 2003?
Hi all, We're looking at upgrading from CRM 3 to 4.0. Most of our users are at MS Office 2003 and we'd like to keep that way for now. I understand 4.0 is supposed to be "compatible" with MS Office 2003 but I've been researching the issues. Are there any known compatibility issues between CRM 4.0 and MS Office 2003? Will using 2003 instead of 2007 cause any loss of functionality in CRM? (Such as disabling the tracking token.) Thank you, Mohamed Both Outlook 2003 and 2007 works fine with Microsoft CRM 4.0. We have a mix of Outlook versions being used everyday for the l...

After install CRM 3.0 beta, logon error: Authentication Error.
I was installed Microsoft CRM 3.0 Beta(90 days trial PID)- Professional Edition But after installed CRM 3.0 beta, logon error: Authentication Error. I think the trial version should can be used. :( Any answers? "CEO" wrote: > I was installed Microsoft CRM 3.0 Beta(90 days trial PID)- Professional Edition > > But after installed CRM 3.0 beta, logon error: Authentication Error. > > I think the trial version should can be used. :( Due to NDA, questions related tot he Bea builds should be addressed in the private newsgroups. -- Matt Parks MVP - Microsoft CRM &...

Using =now() to display date format like "mmdd"
I am building a quoting tool and for the quote number, I am trying to concatenate "QUO", the first 3 characters of the customer name, and the month & date. So what I want is: QUO-SON-0225 But when I use the "NOW" command for the date, I get: QUO-SON-39869.3365444444 How can I reformat the result of the "NOW" command to get just the "mmdd" that I'm looking for? Hi Try ="QUO-"&LEFT(Customer,3)&"-"&TEXT(NOW(),mmyy") where Customer is the cell ref containing the Customer name -- Regards Roger Govier <r...

Print Customer Statement (or similar report) w/o closing billing c #2
Print Customer Statement (or similar report) w/o closing billing cycle Is there a way to print a customer statement or similar report before closing billing cycle? None of the reports available would show/print a statement. Anyideas or suggestions? Thanks In Manager go to journal menu and choose Close Billing cycle. On the screen that pops up select Reprint Customer Statement in the Option box at the top. This will allow you to print a statement by date range without closing the cycle. It will warn you that you are closing the cycle but it really isn't when you just reprint so you c...

Installation and Deployment Certification 4.0
Hello, I am studying to certification for CRM Installation and Deployment of Microsoft CRM 4.0. I would like to know tips to achieve my goals. Any help would be appreciated. Thanks in advance. MATTBIONDI read the Implementation Guide - specifically the Planning and the Installing documents. Dave Ireland "Juliano" <mattgame@uol.com.br> wrote in message news:9F6B0D99-BAE8-4BD1-ADBC-1CADEBF1A731@microsoft.com... > Hello, > > I am studying to certification for CRM Installation and Deployment of > Microsoft CRM 4.0. I would like to know tips to achieve my...

Building a new Test Server to test 2.0 on
Hello, I am setting up a new test server to test out 1.2 HQ RMS upgrade to 2.0 HQ RMS. In setting up the 1.2 HQ RMS, I copy the 23GB Database from the HQ RMS Backup Server. Then Restore it the database in HQ RMS on the test server, it did not prompt me to upgrade to full 2000 SQL Server database. I can open the database fine and query it. I was thinking there was a 2GB limit to the version on the CD-ROM, would it because I copy the database from a server with the full version 2000 SQL? I am needed to get feedback on this before moving ahead with the 2.0 test. Ed ------=_NextPart...

Text formatting
Hi gurus... I would like to run it by you, experts... Here is the problem: I woul like to build a macro that format the text so that the first letter of each word is a capital... For Instance a1= "perform backup" I would like the macro to transform the entry in a1= "Perform Backup"... Can someone provide guidance as to what the macro should look like or what function to use? Many thanks from Brew City Michel Try in B1: =PROPER(A1) Copy B1 down If desired, copy col B and overwrite values in col A via: Copy col B > Paste special on col A > Check "Values...

Post Dated Cheque in Receivables
Hi to All GP Tech, Here i have some confusion in Receivable/payable Module GP-8.0 SP-3,having complete Module sales-Receivable,SalesOrder Processing,Purchase&Payable,Inve ntory & Finance. 1-When preparing A Cash Reciepts for normal cash cheque or cash its straight prepare print and post , but what happen when postdated cheque comes as 12dec2006, $50.00, from Customer 'ABC' what should i do , how system and posting take care on the cheq realizeation date and how accounts GL side and as well as customer side will behave - is their any way it can be record , their is no p...

how do i make a date change automatically if i change one before .
I have a production schedule within an excel document. I have a start date and a release date. Between these dates I have different dates that represent deliverys, approvals and review dates. These dates are sometimes changed due to weather, client issues, etc. Everytime I have to change a date, I have to look at a calendar and make the changes in each cell after the first change. Is there a way to format all the cells after the first start date, so if I change one date, all dates after that will change the same amount of days, leaving out weekends and national holidays. Take into...

format of number keeps changing
I have an excel spreadsheet and in 2 of the columns i have a list of numbers, the format of the cells for each column are the same but on the second column when i type in a number and hit enter to go to the cell below it changes the digit at the end of the number to a zero, for example if i put in 12345 when i hit enter it changes to 12340, this doesnt happen on the column beside it even thought the formatting of the numbers for each cell in both columns is the same. Any ideas????? Is the number more like 1234567890123456 and it becomes 1234567890123450 ?? If so, you're ...

Excel changing date format after VB has run
Hi I have a sheet 1 with a combo box and a blank field. When something is selected from the combo box a date gets populated in the blank field. The date is custom formatted as mmm-yyyy. On sheet 2 I have a table with the options in the drop down box and a date next to them. When a selection is done, the code does a VLOOKUP and populates the data in Sheet 1 in the blank field. Dates are also mmm-yyyy in the Sheet 2. The problem is, say the date in Sheet 2 is 01/12/2010 displayed as Dec-2010. When it is put into sheet w, it flips it to 12/01/2010 so says Jan-2010. Any idea w...

RMS v2.0 HQ Client issues and suggestion
The new license restrictions on HQ Client are bad Bad BAD. Back in the glorious past known as RMS v1.X, I could install HQ Client on as many PCs as I wanted. My registers didn't have to rely on another system to fulfill HQ A/R requests for processing payments or Check Stores requests. Now, I have HQ Client on one PC per store and the registers rely on that one PC to handle all requests. If HQ client is busy processing a WS401 and maybe a WS340 or two and the obligatory WS250s that go along with the WS340s, then the A/R or Check Stores request times out. My HQ Clients are constant...

Re: Large format printer
it is helpful if you describe what your large format printe ris. Encad 36Pro? Epson 10000? ??? > I am trying to set up a document 11"x17" in size to print on a large form= at > printer. I can't seem to set up the document. When I view the document, = it > appears to view the document on 4 separate sheets of 11"x8.5" paper. How > should I set this up? > -- > Ronnie Mac Townsend Adcom Graphics, Fairfield, C A www.adcomgraphics.com Powered by the E-mail PIM - Info Select - www.miclog.com ...

Nesting IF Statement in excel 2007
I am trying to do an if statement on a second sheet, there are three cells that have the same drop down list in it. I tried this bu am getting an error. =IF('EV Assessment'!M43="Second Range",2,0,IF('EV Assessment'!M42="Second Range",2,0,IF('EV Assessment'!M41="Second Range",2,0)))) I tried it with three and four parenthesis at the end and no change. Thank you Try the below =IF('EV Assessment'!M43="Second Range",2, IF('EV Assessment'!M42="Second Range",2, IF('EV Assessment'!M...

Unable to install CRM 3.0 client on Outlook 2003
I originally attempted to install the CRM 3.0 laptop client for Outlook 2003 on a standalone WinXP machine at a customer's home office (in a workgroup; not joined to the customer's domain). Received the following error message after the first list of missing components were successfully added during the installation prerequisite phase: ================ "Action Microsoft.CRM.Setup.Common.Analyzer+CollectAction failed. %1 is not a valid Win32 application." ================ Pressing the "Ignore" button at this prompt results in the following message: "Data P...

Combined date time cell to separate date & time components #2
From file dump have combined date time cells eg 14/04/03 14:20 (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) See the reply to your post in the microsoft.public.excel newsgroup. Please don't post the same question to multiple groups. It just wastes the time of those replying to a question that was...