+ Reply to Thread
Results 1 to 5 of 5

HLOOKUP and date weirdness....

  1. #1
    Forum Contributor
    Join Date
    06-21-2006
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2007
    Posts
    108

    HLOOKUP and date weirdness....

    Hi, I'm trying to do a HLOOKUP from a different workbook, basically it references dates on both sheets. Now I have noticed that on the sheet that the HLOOKUP formula is in, the date 20/08/2007 when not formatted to date is 37852, whereas the same date in the workbook being referenced is 39314, so the HLOOKUP is not working? why are the 2 dates different? If I open a new workbook the date is 39314. so i am assuming this is the correct figure. Why is my one workbook largely different?

    Thanks in advance
    Last edited by neilcarden; 07-30-2007 at 09:27 AM. Reason: .

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Excel's default date system starts counting from 1900 but you can change to 1904 date system. Looks like your first worksheet uses 1904 date system.

    You can change it using Tools > Options > Calculation > uncheck "1904 date system"

    Note: If you change this all your existing dates will change. 1904 date system also allows negative time and dates so, if it was changed to enable that changing back might not be a good idea...

  3. #3
    Forum Contributor
    Join Date
    06-21-2006
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2007
    Posts
    108
    I thought it might have something to do with that. Yeah ideally we need that ticked as we use minus figures on this sheet too.... is there any way round this, without starting from scratch? :s

  4. #4
    Forum Contributor
    Join Date
    06-21-2006
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2007
    Posts
    108
    It's ok, I have just amended the HLOOKUP to C239+1462.... 1462 being the difference in days of the 2 dates due to 1904 system.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Another option is to change to

    C239+1462-DATE(4,1,1)

    which means the formula will give the same result even if you change the date system on that worksheet

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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