# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Removing the 00/01/1900 date when looking up another cell in another worksheet

## swifty

Hi was wondering if anyone can help. 

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 

Any help would be very much appreciated

----------


## mubashir aziz

Suppose data is in Sheet1 cell A1 and you want to look in Sheet1 then simply put this formula in Sheet2 cell A1

=IF(Sheet1!A1="","",Sheet1!A1)

----------


## zbor

Or =IF(Sheet1!A1>0;Sheet1!A1;"")

(This will show blank if number in sheet 1 is negative too)

----------


## swifty

Not sure that really helps, I may be being stupid (Im relatively new to excel) basically if have a document on sheet two with variance sums and dates, on the first sheet is a cover which pulls together all the most important info. I have had issue's like this before where if no data is inputted it shows the "#VALUE!" error, to fix this I found pressing F2 followed by Control + Shift + Enter, this left the cell with a zero when no data was inputted, is there a similar fix for dates?

----------


## zbor

Can you put example?

----------


## swifty

yes, 

on sheet 1 there is a cell (C9) which shows a requested live date, which is then referenced in the cover sheet (C4). If the sheet 1 cell isn't formatted then it shows a 0 which would be fine but when a date is entered it shows a five digit number an not a date, but when it's formatted as a date it shows the 00/01/1900 date. given that there are 10 sheets that all require the go live date to be referenced, even if no data is inputted. 

When i double click on the unformatted cell it does change to a date format, but this workbook needs to be used by other users other than myself so leaving it like this is not really an option.

----------


## zbor

I ment upload excel worksheet

----------


## swifty

sorry, see attached, this shows the 00/01/1900, the cell that is referanced is b2 in sheet 2, there is nothing inputted into this cell and B1 on sheet 1 shows the 00/01/1900 date i want to remove

----------


## daddylonglegs

I'd change the formatting of B1....

Select B1 > Format > Cells > Number > Custom

type or paste in the box

dd/mm/yyyy;;

Note the two ;; these are essential

----------


## swifty

Thanks for that, problem solved!!

----------


## taourimr

Thanks daddylonglegs, this did solve the same problem I had.
Can you explain how the ;; work? and what does it mean?

dd/mm/yyyy;;

----------

