+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    04-12-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    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

  2. #2
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    531

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

    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)

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,429

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

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

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

  4. #4
    Registered User
    Join Date
    04-12-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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?

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,429

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

    Can you put example?

  6. #6
    Registered User
    Join Date
    04-12-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,429

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

    I ment upload excel worksheet

  8. #8
    Registered User
    Join Date
    04-12-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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
    Attached Files Attached Files

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,674

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

    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

  10. #10
    Registered User
    Join Date
    04-12-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Thanks for that, problem solved!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1