9 Replies Latest reply: Apr 30, 2012 7:52 AM by KOENIG Yvan
dave2.0 Level 1 Level 1 (0 points)

hello!

im using numbers to edit an excel sheet with a huge amount of wrong formated dates....

therefor i split the wrong dates into day/month/year and wanted to use the command =DATE(year;month;day)

IT DOES NOT WORK!!!!

year and month provides exact results, but days produce a shift of two days?!!? how come???

so for example:

yearmonthday
date
090405=date(a2;b2;c2) => produces 07.04.09
090507=date(a3;b3;c3) => 09.05.09




how come???

i dont get it

 

please help! i need it for a project at university

 

br

DAVID

 

here is a screenshot... (im using the german version)

bug numbers.jpg


numvers, Mac OS X (10.7.3)
  • 1. Re: numbers / wrong date
    Barry Level 7 Level 7 (29,180 points)

    Hi David,

     

    I don't know.

     

    One note on DATE, though, from the notes in the Function Browser entry:

     

    Year: The year to include in the value returned. year is a number value. The value isn’t converted. If you specify 10, the year 10 is used, not the year 1910 or 2010.

     

    Returning to your question, I did notice that the three dates visible in the Start Date column of the table above the large one in your sample do display dates in column that match the values displayed in the four columns to the left of Start Date.

     

    What differences are there in the way these dates and the ones adding two days are derived?

     

    Regards,

    Barry

  • 2. Re: numbers / wrong date
    dave2.0 Level 1 Level 1 (0 points)

    Hi Barry,

    The table above is the just a small part of the 08 table (i tried to match the format)....

    There it worked without a problem.... but for 09 i have this unexplainable bug

    i switched to excel now to solve the prob and there it works as expected!

    i just dont get where the calculation is wrong (i formated everything as suggested by numbers)

    THANK U FOR YOUR HELP

    but i guess this is a bug in numbers

     

    br,

    David

  • 3. Re: numbers / wrong date
    Barry Level 7 Level 7 (29,180 points)

    "but i guess this is a bug in numbers"

     

    Possibly. But getting the correct results here (with entered data, rather than data imported from Excell) could indicte the 'bug' is a localization artifact.

     

    I'm hoping someone with a better knowledge of the internal workings of Numbers picks this up.

     

    If not, it's probably worth your filing a bug report via Provide Numbers Feedback in the Application (Numbers) menu in Numbers.

     

    Regards,

    Barry

  • 4. Re: numbers / wrong date
    Level 8 Level 8 (41,760 points)

    May you look at :

    https://discussions.apple.com/message/18245131?ac_cid=142432#18245131

     

    Maybe you are facing the same problem with a preferences file.

     

    Not that, the app behaves the same when used in French version.

    2012-04-28T11.49.21.png

    I never saw that because I use always four digits year values.

     

    Yvan KOENIG (VALLAURIS, France) samedi 28 avril 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • 5. Re: numbers / wrong date
    Level 8 Level 8 (41,760 points)

    In fact, it appears that Apple engineers use a wrong algorithm.

    It strikes also upon AppleScript.

    The oddity was introduced (at least for AppleScript) with Snow Leopard.

    Here is a table showing the algorithm at work :

    2012-04-29T17.53.49.png

    The algorithm is OK for the Gregorian era.

    It's facetious with older dates.

     

    Yvan KOENIG (VALLAURIS, France) dimanche 29 avril 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • 6. Re: numbers / wrong date
    Level 8 Level 8 (41,760 points)

    Here is a more detailed table.

    They just forgot to take care of leap years and of special cases : 400, 800, 1200

     

    2012-04-29T19.16.29.png

     

    Yvan KOENIG (VALLAURIS, France) dimanche 29 avril 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • 7. Re: numbers / wrong date
    Badunit Level 6 Level 6 (10,815 points)

    The problem is that you are getting a date for the year 0009, not the year 2009.  There was a shift in the calendar and the algorithm is accounting for that shift.  I don't know if it is doing it correctly but that's what it is doing.

     

    You think it is working in Excel but it is not, it is giving you a date in the year 1909, not 2009.

  • 8. Re: numbers / wrong date
    dave2.0 Level 1 Level 1 (0 points)

    hi badunit!

    thanks for the information!

    i acually had to solve the issue in excel by manually setting it to 2009 (it really applied 1909 for my spreadsheet... dont get why programmers would assume 1909 instead of 2009 as default....)

    br, david

  • 9. Re: numbers / wrong date
    Level 8 Level 8 (41,760 points)

    I guess that it's because they wrote their code during the XXth century when padding the year num with 19 made sense

     

    Yvan KOENIG (VALLAURIS, France) lundi 30 avril 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k