Thread: Removing the 00/01/1900 date when looking up another cell in another worksheet

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

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)

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?

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.

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

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

Thanks for that, problem solved!!