Stop 01/00/1900 Showing In Cell

Dec 20, 2006

I have a cell that has a formula in it and is also Excel Dates & Times coded. Hence when the repsective cells are empty and there is no values to be calculated the date "00-jan-00" shows. how to prevent this from showing up allowing for a blank cell to only show?

View 4 Replies


Stop #N/A Showing On A Spreadsheet

Sep 29, 2008

I have a spreadsheet gathering information on vehicles. One of the pieces of information is a 'Date Off Strength'. For the vehicles still on strength I have the symbol '#N/A' showing,, this is linked to a sheet using a VLOOKUP formula, is there a way to stop this symbol showing

View 4 Replies View Related

Stop Pages Showing

Jan 3, 2009

i want it to stay on working sheet asit prints out the selected sheets below.
is there anyway to stop this from showing the sheets? ie stay on working sheet

View 14 Replies View Related

How Do You Stop 0's From Showing Up On A Chart

Dec 5, 2008

I have data that has values showing 0 and I don't want them showing up on the chart. I have excel 2003 and 2007 that I need it to work for.

This is what I am using for excel 2007 and it works fine.

=IF(COUNTIF('Closed Transaction'!I6:I38,"Friends")=0,NA(),COUNTIF('Closed Transaction'!I6:I38,"Friends"))

I need a forumla for excel 2003. The one above still shows the 0's on the chart.

View 9 Replies View Related

Stop Zero Showing In Formulas

Nov 13, 2006

when I apply an = 'Worksheet1'!A1. formula to a cell, the respective cell will contain a zero. Is there any way that the cell can be shown as completely blank and still hold this formula?

View 3 Replies View Related

Stop / Start Characters And Showing 0s

Sep 19, 2013

I'm currently creating an inventory tracking spreadsheet for my restaurant.

I'm using ="*"&A(x)&"*" to create the necessary barcodes. Some of my inventory numbers have leadings zeros that need to be included. The formula above is eliminating the 0's and how to get them to show. I've tried a custom number formatting but that isn't working for me.

View 5 Replies View Related

Stop Row/Column Heading Showing

Aug 3, 2006

I am building a small accounts package and need certain work books to open with no toolbars or row and colum headings showing.

View 7 Replies View Related

Stop Macros Showing In Macro Dialog

Dec 9, 2006

I've forgotten how to prevent the macros from showing in the macro dialogue box.

I'm sure it's got to be something with the VBA editor, but I can't figure it out.

I've got some files where the macro list is hidden and I'd like to do the same again, but for the life of me....I'm stumped. I'm sure it's something very simple.

I've protected the Worksheet & Workbook.

I've done some searching...."macro hide", "hide dialogue" etc. but can't seem to find what I'm looking for.

It's been a while since I had to amend code, as everythings been running very smoothly.

View 5 Replies View Related

Current Year In Cell Not 1900

Jan 15, 2009

I've conditioned formated some cells so that someone can type a number in and a date will appear in the cell.

Problem is if someone types 20 in the cell.. excel reverts back to Jan-20-1900

How can I get the current month and year when a number is typed in the cell?

Did excel exsist in the 1900?

Solution - I used the if command in A2 like this

View 6 Replies View Related

Removing The 00/01/1900 Date When Looking Up Another Cell In Another Worksheet

Apr 12, 2009

I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2

View 9 Replies View Related

Produce Blank Cell If Date Is 00/01/1900

May 10, 2010

I have a column of data that requires the cell to be blank if the value is 00/01/1900. The data is linked to another cell. IE: cell E3 is linked to cell $D$3 The cell is formatted for dates in the format shown.

View 7 Replies View Related

Display 'Date' Cell As Blank Instead Of Default Year 1900

Nov 19, 2009

Three columns.

A - Date last checked
B - Due Date
C - Actual Date checked

Currently column B is formatted to Date and simply has =A+84 and will display a date 3 months in future. However if there is no date in column A, then column B displays a default 1900 date.. Is there a way of making this blank if there is no date in col A?

View 5 Replies View Related

Dates Before 1900

Sep 3, 2009

I'm an accountant, so I've always dealt with relatively current dates. Now I'm trying to do some geneology work, and I find that I can't put in a date prior to 1900. For example if I input "3/5/1870", it comes out as text. If I input "=date(1870,3,5)", it returns 3/5/3770 -adding 1900 years to my date. I don't need to do anything mathmatically with it, but it would be nice to have it return "March 5, 1870".

View 2 Replies View Related

Date Defaults To 01/00/1900

Aug 25, 2009

I am working with an Excel file that was created in 2001, has not been updated since.

In the first column on the first tab, is a date field, starting from 12/31/2000.

I try to change this to 12/31/2008, and I get the return date of 01/00/00. When I select the cell, the formula bar indicates 12/31/2008, but the cell shows 01/00/00.

If I change it back to 12/31/2000, it is back to normal. I tried to enter 2/3/2009 in another cell, converting to date format, and the same thing: 0/1/00 (Jan 0, 1900)

This is a US date format.

View 7 Replies View Related

Displaying A Date Of 00/01/1900 If The Value Is Zero

Feb 4, 2008

I currently have to display a large amount of data on a separate report. I have a master sheet which has columns and cells linked to another sheet, then a further sheet which has cells linked to the master sheet. The problem I am getting is when I link some of the date columns using a formula in the 3rd sheet, as this is looking at a cell that has another formula in on the other sheet it is displaying a date of 00/01/1900 if the value is zero, is there any easier way of getting the data to just display a blank as this is messing up the database it is being exported

View 9 Replies View Related

Date 1/0/1900 Displays Instead Of Blank

Feb 8, 2009

The result of my formula should be blank, but 1/1/1900 appears instead and I can't get rid of it. Does anyone know why or how to fix. I can't just change the font color as it screws up other formulas.


In this case the Vlookup is referring to an apparently blank cell with no formula in it. I googled 1/0/1900 and saw a few references that said formatting a cell with zero in it as a date would cause this result, but there is no zero in this case.

View 9 Replies View Related

Date Converting To 1/0/1900 On Entry

Jan 30, 2008

I have a old spreadsheet file which I opened up in Excel 2003. It has a few worksheet tabs. 2 worksheet tabs seem identical. I will reference it as Worksheet1 and Worksheet2. In Worksheet1, I enter a date i.e. 1/25/2008 in any cell. It enters as inputted. But in Worksheet2, I enter the same date i.e. 1/25/2008, it appears as 1/0/1900. No matter what date I enter in Worksheet2 in any cell, it always appears as 1/0/1900. Now when I look at cell in the formula toolbar, it sees as a Math Formula meaning, it looks like this: =1/25/2008. How do I correct this Worksheet to recognize any date inputted, not to see it as a Math Formula?

View 4 Replies View Related

Converting 1900 Date System To Text

Jan 30, 2009

How do you convert a range of dates in this format: 39843 (i.e today 30th of Jan 2009). To text like: 20080130. Secondly, For the same date, how do you make the weeknum function give answer '05' and not just '5'

View 2 Replies View Related

VB Code To Remove 00/01/1900 Dates From Spreadsheet

Feb 6, 2014

I have a spreadsheet which was formatted from VB code. Within the sheet there are four date columns where some dates show as 00/01/1900 (due to them having '0' in at the time of running the code). I am trying to run some code to blank out all cells showing the 00/01/1900 date, but the code just isn't touching it. I reverted to simply recording the 'find and replace' function and inserting into my code, but those irritating dates still remain.

Code I have used is as follows:

[Code] .....

View 4 Replies View Related

Indexing Dates, Blank On One Sheet Displaying As 00/01/1900

Feb 12, 2010

I am indexing dates from one workbook to another.

There are some blanks in the list that I am using, and I need to the blanks to stay as blanks in the sheet that I am indexing to, however they are displayng as 00/01/1900.

View 4 Replies View Related

Stop Macro: Button To Hit Or Better Just Some Keys To Hit To Stop It Without Using The Ctrl+alt+del Which Closes Everything

Dec 13, 2006

my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.

View 4 Replies View Related

Looking Up Cell Data And Showing Results From Adjoining Cell?

Jan 9, 2014

I would like to be able to enter the data into the grey cells and then have the yellow cells auto generate a result. The only problem is that the required value in cell B5 is from when the entered value in B4 is looked up in the table, rounded UP to the next highest value and then the answer from the next cell.

So, if I enter in B4 a value of 500, a lookup is made in the table and the next highest value would be 553 with the correct answer shown in B5 being 13.

View 2 Replies View Related

Showing Values In A Cell

May 29, 2014

I am using dglinfo with if statements, and I know that the spreadsheet is updated but the only way I can show the value in particular cell is by hitting the fx. Is their a way to show all the values at once instead of going cell by cell?

View 2 Replies View Related

Cell Limits Showing ####'s

Nov 17, 2008

I am entering text into a cell and as soon as I go over 241 chars, give or take, the data turns to a row of ####'s when I get out of the cell. Help says the limit is 32K and I have wrap text on. What am I missing? The data displays OK when the cell is current, but goes away when the cell is not . When I preview a print I have the ####'s. I am using Excel 2000.

View 9 Replies View Related

Leading Zero Not Showing In Cell

Dec 31, 2008

A1 has the value 7

I want the code to take that value and add a leading 0 and put the result as 07 in B1.

View 6 Replies View Related

Formula Showing In The Cell Itself

Jan 29, 2009

I created a formula in a cell and it worked fine but then I created the same formula in the cell just below it and I changed some of the numbers and when I hit the enter button the formula showed up in the cell and not the final result which would be the answer to the formula. Does anyone know why it would do this? I have an example below....

View 6 Replies View Related

Formula Showing In The Cell But Not The Result

Aug 23, 2002

I have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.

The same formula could be another place in my worksheet and work fine.

I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.

View 9 Replies View Related

Cell Is Showing Function String

Feb 8, 2007

The following concatenate function was working, then mysteriously stopped. When working it returned the data that I requested, now if I click on any concatenated cell and hit enter I get the function string, see below.

='Input DATA'!C2&" "&'Input DATA'!B2&" "&'Input DATA'!F2

InputData is my tab that is being referenced. All other functions/formulas are working as designed. The data in the referenced cells is still there and I changed from text to number to general, etc but I still get the same result.

View 9 Replies View Related

Showing The Contents Of A Cell Based On Another Cell

Nov 9, 2005

one cell gives me the MAX value of a range of cells
I want to then use that value to refer back to the range of cells and return the value one column to the left

This formula returns "A4" which is the cell number for this result I want to show but it just enters A4 not the contents of cell A4


View 9 Replies View Related

Sum With Date Criteria Then Showing When Selected Cell

Feb 23, 2014

i have set of data excel with the below ;


how do sum with selected cell criteria by month (likes drop down list) , e.g.

sum of February, or sum of March, etc...

with helper column, if i selected month of February, showing data sum February or if i selected month of March, showing sum data March...

View 7 Replies View Related

Copyrights 2005-15, All rights reserved