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
572 Views

Similar Articles

[PageSpeed] 33

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:

Tracking Dates For Future Occurrences
Can this be done? I want to track a yearly review. I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. Can this be done? I am new to all this, thanks.. In 2007 Also.. "Knee2no" wrote: > Can this be done? I want to track a yearly review. I would like the date, > once entered - say 6/1/2009, to conditionally format to change yellow 30 days > before, then red 15 days before, and then to stay red until the date is...

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

3.0 Customization
Is it possible in 3.0 to have one set of screens appear for one group of users and another set for another group. For instance, could our service people only see the service screens while our sales people only see the sales screens? I know I can restrict access to different areas, but we want to have a totally different look and feel for each group... Sorry - I dont believe this can be done "Matt Harvey" <rifleman@gmail.com> wrote in message news:OR2vU$3GGHA.740@TK2MSFTNGP12.phx.gbl... > Is it possible in 3.0 to have one set of screens appear for one group of >...

Report CRM 3.0
Hi, I would need to find out the detailed procedure (step by step) to customize a report…. Could anybody inform about any links or documents concerning this issue? Thank's Marco I'm not sure if it's detailed enough, but the technical training manual has a section on creating and customizing reports. You can find it here: http://www.microsoft.com/businesssolutions/crm/using/whatsnewtechnical.mspx HTH, -- Jeffry van de Vuurst CWR Mobility www.cwrmobility.com -- "Marco Rocca" <Marco Rocca@discussions.microsoft.com> wrote in message news:CEF80683-EC26-456C-82C...

Statements #2
How are statements sorted? Alphabetically by name or by account number? Does anybody know? -- Any help is appriciated, Deb Mine are done alphabetically by name. -- Elizabeth M. "Deb" <Deb@discussions.microsoft.com> wrote in message news:D58FEF32-7509-473B-A554-6A48E02697CF@microsoft.com... > How are statements sorted? Alphabetically by name or by account number? > Does > anybody know? > -- > Any help is appriciated, Deb ...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Formatting #13
Hi How can i have codes in this form 00.00.0000.00, & i wanted to sum to the values below like next code, 00.00.0000(+1).00 I'm tired to format but always sum in the last 2 digits 00.00.0000.00(+1), what can i do Someone can help me Thanks How did you put 00.00.0000.00 in the cell? Did you type 0 and then give it a custom format? If yes, try changing your custom format to: 00\.00\.\0000.00 Then add 1, but make sure that the resulting cell also has this custom format. This is really a funny formatted number with 2 decimal places now. Carla wrote: > > Hi, > How can i ...

Can you create custom activities? MSCRM 3.0
Hi, Is there a way to create a new custom activity instead of customising an existing one? I have created a custom entity called 'Chat' utilising an IFRAME. All works well but this entity really should be an activity considering it's properties. In fact I've just been introduced to MS-CRM 3.0 and don't really understand what the difference is between an entity and activity. Would anyone shed the light for me? BTW, I think 3.0 looks great. Gotta admit it's improved. Cheers. Ty In my experience, you cannot create custom activities. In fact, I have been dire...

conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have a notifiction sent to you when a date on a spreadsheet has expired? > how do you add a phrase to a field if the filed is blankl, What "phrase" do you mean? A Comment? A value? also, can you have > a notifiction sent to you when a date on a spreadsheet has expired? Maybe you can apply an open event (date to be tested being say in F1): Private Sub Workbook_Open() If Range("F1") < Date Then MsgBox "Date expired" End If End Sub Regards, Stefi ...

Excel 2002 converts 'S' to 0 when pasting from Clipboard
I came across the following problem: I copied some tabular data from IBM Personal Communications into the clipboard (yes, I am still a user of good old 3270 applications). Then I pasted the data into Microsoft Excel 2002 and all cells containing a 'S' became a '0' (number zero). Next I did some tests and found out that every single uppercase 'S' that is transferred to Excel using copy/paste is translated to '0'. This would not happen with other letters or with words containing an 'S'. Using 'Paste special' I can choose to insert my Clipboard a...

Converting date from an external source
I am having an issued with converting a date from external data source. the data has the timestamp in the general date form mm/dd/yyyy 00:00:00. I want to convert the date to mm/dd/yyyy format so when i run a query for a single day it will return the data for that date, I can currently return the data but i have to set the parameter in the mm/dd/yyyy 00:00:00 format, i want to simply return the data by setting the parameter in the mm/dd/yyyy format Don't confuse how data is stored with how it is presented. As long as you import the date into a field defined as a Date data type, you...

How do I import from LotusOrg 6.0?Import command only has 5.0
I am trying to import my calendar data from Lotus Org V6.X. Under the file command, it will only import from V5.X. Does anybody have any help for me since I would like to convert to Outlook from Lotus Organizer. Don Kiamie donalbert@mindspring.com In news:32C8F514-3EA5-4802-B1A4-F9C66E77293A@microsoft.com, DonAlbert <DonAlbert@discussions.microsoft.com> typed: > I am trying to import my calendar data from Lotus Org V6.X. Under > the file command, it will only import from V5.X. Does anybody have > any help for me since I would like to convert to Outlook from Lotus > ...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Conitional Formatting
Hello. I have two fields in a subform, "User" and "IT Announcement" I would like to do conditional formatting to this effect: On ther "User" field: If "IT Announcement" = Yes then make the "User" field turn blue (I would choose the color blue from the conditional formatting selection. How would you write this? Thanks. Iram/mcp On Tue, 23 Oct 2007 14:59:01 -0700, Iram wrote: > Hello. > I have two fields in a subform, "User" and "IT Announcement" > I would like to do conditional formatting to this effect: > On...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

IF statement based on True/False that activates a certain list box
I've been asked to create a formula that will react to either "yes" or "no" entered into a cell which will show options in another cell based on a certain list. Example: Cells D3:D5 contain the values Monday, Tuesday, Wednesday (list named BegWeek) Cells E3:E5 contain the values Thursday, Friday, Saturday (list named EndWeek) Cell A3 accepts either "yes" or "no" entry only from a list If A3 = "Yes" then B3 will = drop down list BegWeek If A3 = "No" then B3 will = drop down list End Week I don't even know if thi...

FRx 6.7 Compatability with GP 10. 0
Hi, Is FRx 6.7 is campatable with GP 10.0? I have installed Frx 6.7 with and using successfully as local cleint but as requirement I need to run the same Balance Sheet Report which I have created on my client machine want to run on the different client machines withou installing the FRx. Do I need to installed FRx on each client machine? Please give the details. Your immediate help will be greatly appreciated. -- Developer Yes. Frx 6.7 is compatible with GP 10.0. Make sure to apply the latest service pack. I am not sure I understand your question. Are you asking if you need to c...

Strange behaviour: show/hide formatting symbols reveals old change
In Word 2007, I'm getting some strange behaviour in a document that was authored by someone else. Track Changes is switched off, all changes have been accepted, and everything looks as it should in whichever view I happen to choose (Print Layout, Draft, whatever). But when I click to show formatting symbols (in whatever view) a whole lot of old changes - deletions AND insertions, ostensibly all accepted, and from before the document got to me - appear in the document, making it quite tricky to work with. These old changes are impervious to anything I try to do with them E...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Date/Time Show When Changes Made
Hi, When changes are made in a table or form, I want the date and time to appear showing me when changes were made in any of the fields. How can it work? MTIA. It's not possible if you're working directly with tables. Using foms, you have to put code into the form's BeforeUpdate event to update the row's LastChanged field (which, of course, you have to add to the table yourself) You might find what Allen Browne has at http://www.allenbrowne.com/AppAudit.html to be useful. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Beame...

Excel chart linked in Word gets formatted when updateing.
Hi, I have a bit of an annoying issue with excel and word 2007, I have a Chart in excel, and the text on the horizontal axis is rotated 270 degrees,This Chart is linked in a Word document and when i do an update of the linked chart the text gets all messed up. Both of the files are saved in 97-2003 format. ...

formatting of charts changes when copying from excel 2000 to 200.
When I copy a chart from Excel 2000 and paste it into Excel 2003, some of the formatting is lost. In particular, scale and axis formatting. Is this a programming issue or can it be corrected easily. Thanks Hi, First one would answer why would you copy charts from 2000 to 2003, why not make them in 2003? Second and more important - how are you copying them - there are maybe 20 possible methods of copying a chart from one program to another. Please tell us exactly which steps you use to do the copying. Also, exactly what formatting are you loosing, what do you get instead? When ...