Blank cell showing 00/01/1900

[mesteptoe]

I have a series of spreadsheets. On one of these (say Sheet A) there is a cell (Cell X) where the user inputs the date when it is last viewed. However, at the outset, it is an empty cell but formatted for a date.
On another spreadsheet (Sheet B) there is a facility whereby it displays the date (in cell Y) when spreadsheet Sheet A was last viewed. This works fine once a date is entered in Cell X (Sheet A) but, initially, when the cell X is blank Cell Y shows as 00/01/1900. How can I get Cell Y to also be blank?

The same also happens when I try to pass information between two separate workbooks, i.e. Cell Y in a different workbook to Cell X.

Any suggestions?

Thank you

Graham

 

[GlennUK]

When you reference an empty cell in a formula, Excel treats it as zero ( that includes simple linking formulas from one workbook to another ). ( and zero in date format comes out as 00/01/1900 )

You can either use a formula like this:
=IF(cellA="","",cellA)

or use custom number formatting to suppress the zeros, like Format Cells/Number/Custom:

[<1]"";dd/mm/yyyy

 

[Richard Schollar]

Hi Graham

I take it you are using a formula along the lines of:

=SheetA!CellX

Instead, use a formula like:

=IF(ISNUMBER(SheetA!CellX),SheetA!CellX,"")

 

[mesteptoe]

Thanks Richard, the ISNUMBER formula seems to do the trick

Regards

Graham