ערכים סידוריים של תאריכי Excel ב-Delphi: ‏1900 לעומת 1904 ו-numFmt

פתח גיליון אלקטרוני, לחץ על תא המציג 2026-06-19, ושורת הנוסחאות עדיין מציגה תאריך. קרא את אותו תא מ-Delphi ותקבל את המספר 46192. שתי התצוגות נכונות, מכיוון ש-Excel מעולם לא שמר תאריך בתא זה. הוא שמר מספר סידורי, ספירת ימים, וצירף עיצוב מספר שאומר למסך לרנדר את הספירה כתאריך קלנדרי. אין טיפוס תאריך בערך התא. יש מספר וכלל תצוגה, וכלל התצוגה הוא הדבר היחיד שמבחין בין תאריך לכמות פשוטה.

הפרדה זו היא המקור לכל באג תאריך שספריית גיליונות אלקטרוניים צריכה להתחמק ממנו. מספר סידורי לבדו אינו אומר איזה יום זה, מכיוון שהוא אינו אומר מה היה יום האפס. אותו מספר פירושו שני תאריכים במרחק של ארבע שנים זה מזה, תלוי בדגל חוברת עבודה בודד. ומספר שאמור להיקרא בחזרה כתאריך ייקרא ככמות פשוטה אלא אם כן משהו יבדוק את העיצוב שלו ויזהה תבנית תאריך. כך בנוי מודל התאריכים ב-HotXLS, ומדוע הוא חייב להיות כזה.

תא תאריך הוא מספר בתוספת עיצוב

Excel שומר תאריך כמספר הימים מאז עידן הייחוס (epoch), כאשר השעה ביום נמצאת בחלק השברי. חצי היום במספר סידורי נושא את השבר .5. החלק השלם הוא ספירת הימים. שום דבר בערך השמור אינו מסמן אותו כזמני. מה שמסמן אותו הוא עיצוב המספר של התא: תקן ECMA-376 קורא לזה numFmt, ותא שקוד העיצוב שלו מפרט תבנית תאריך או שעה מוצג כתאריך. הסר את העיצוב והתא יציג מספר; הערך הבסיסי מעולם לא השתנה.

זהו ההסבר מדוע קריאת ערך תא מחזירה Variant שעשוי להיות varDate או Double פשוט, ומדוע עיצוב המספר באותו תא הוא הסימן שקובע למה צד שלישי התכוון. כאשר HotXLS פותח קובץ XLSX, תא נושא הן את ה-Value שלו והן את ה-NumberFormatIndex שלו לתוך TXLSXCell, ואינדקס העיצוב הוא מה שאתה בודק כדי לדעת אם המספר הוא תאריך.

var
  Book: TXLSXWorkbook;
  Cell: TXLSXCell;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('timesheet.xlsx') <> 1 then
      raise Exception.Create('Cannot open workbook');

    Cell := Book.Sheets[0].Cells[1, 1];   // row 1, col 1 (1-based)
    // Value may arrive as varDate or as a plain numeric serial;
    // the format index is the signal that tells them apart.
    Writeln('raw value : ', VarToStr(Cell.Value));
    Writeln('numFmt idx: ', Cell.NumberFormatIndex);
    Writeln('format    : ', Cell.NumberFormat);
  finally
    Book.Free;
  end;
end;

שני עידני ייחוס במרחק 1462 ימים

מערכת התאריכים כברירת מחדל, זו שכל חוברת עבודה של Windows משתמש בה, סופרת מסוף שנת 1899, כך שהמספר הסידורי 1 נופל על היום הראשון של שנת 1900. המערכת השנייה מקורה במקינטוש המוקדם וסופרת מתחילת שנת 1904, כך שהמספר הסידורי 1 שלה הוא ארבע שנים ויום אחד מאוחר יותר. חוברת עבודה מתעדת באיזו מערכת היא משתמשת בדגל בודד. בחבילת OOXML דגל זה הוא date1904 בחלק של חוברת העבודה; HotXLS מציף אותו כמאפיין Date1904 של חוברת העבודה.

הפער בין שני העידנים הוא בדיוק 1462 ימים. אלו ארבע שנים קלנדריות, שלוש של 365 ימים ואחת מעוברת של 366 ימים, סה"כ 1461, בתוספת יום אחד נוסף עבור ההפרש בין שתי מוסכמות יום האפס. המספר קבוע ותוכל לזכור אותו. חשיבותו היא שהוא אינו אפס. מספר סידורי שהועתק מחוברת עבודה של 1904 ופורש תחת חוקי 1900, או ההפך, ינחית כל תאריך במרחק 1462 ימים מהתאריך הנכון, מה שמציג תאריכים שגויים בקצת יותר מארבע שנים וקל לטעות ולחשוב שמדובר בנתונים מושחתים.

מכיוון ש-TDateTime של Delphi עצמו מעוגן למוסכמת 1900, ספרייה הממפה מספרי Excel סידוריים ל-TDateTime חייבת להסיט ב-1462 ימים בשני הכיוונים בכל פעם שחוברת העבודה מסומנת ב-1904. בקריאת מספר סידורי של 1904, חסר 1462 לפני שתתייחס אליו כאל TDateTime; בכתיבת TDateTime לתוך חוברת עבודה של 1904, חסר 1462 מהמספר הסידורי כך ש-Excel ירנדר את היום שהתכוונת אליו. HotXLS מחיל היסט זה פנימית כאשר הוא מבצע סיראליזציה לערכי תאריכים עבור חוברת עבודה שבה Date1904 מוגדר, כך שהערך שאתה מייחס כ-TDateTime מבצע סבב מלא לאותו יום קלנדרי על המסך.

מוזרות שנת העיבור המכוונת של 1900

ישנה מוזרות מפורסמת במערכת 1900. Excel מתייחס לשנת 1900 כשנת עיבור ומקבל את 29 בפברואר 1900 כתאריך אמיתי, מספר סידורי 60. השנה 1900 לא הייתה שנת עיבור, מכיוון ששנות מאה הן שנות עיבור רק כאשר הן מתחלקות ב-400, ו-1900 אינה מתחלקת. היום המדומה הוא התנהגות תאימות מכוונת שנורשה מגיליון אלקטרוני מוקדם שהושק עם הבאג, ונשמרה מאז כדי שהאריתמטיקה הסידורית תישאר זהה לאורך עשרות שנים של קבצים.

התוצאה המעשית קטנה אך קיימת: עבור כל תאריך ב-1 במרץ 1900 או אחריו, המספר הסידורי גבוה באחד ממה שספירת ימים מדויקת הייתה נותנת, מכיוון ש-29 בפברואר הלא קיים צרך מספר. ספריית גיליונות אלקטרוניים משחזרת את המוזרות הזו במקום לתקן אותה, מכיוון שהתאמה מדויקת לאריתמטיקה של Excel היא כל העבודה. תיקון שלה היה מציב כל תאריך מודרני במרחק יום אחד ממה ש-Excel מציג, שזו תוצאה גרועה יותר מאשר נשיאת שגיאת off-by-one בת ארבעים אלף ימים ששום תאריך אמיתי בשימוש עסקי אינו נוגע בו. למערכת 1904 אין יום מדומה מקביל, וזו סיבה אחת לכך שחלק מהארגונים העדיפו אותה היסטורית.

זיהוי תאריך מתוך numFmt

כאשר מספר מגיע מקובץ שמישהו אחר כתב, העיצוב שלו הוא הראיה היחידה לכך שהוא תאריך. תקן ECMA-376 מקצה בלוק של מזהי עיצוב מובנים שמשמעותם קבועה במפרט, ועיצובי התאריך והשעה תופסים טווחים ידועים. מזהים 14 עד 22 הם עיצובי תאריך ושעה כלליים, ה-m/d/yyyy,‏ h:mm המוכרים וקרוביהם. מזהים 45 עד 47 הם עיצובי זמן שחלף (elapsed-time). שני טווחים נוספים, 27 עד 36 ו-50 עד 58, הם עיצובי תאריך ושעה ספציפיים לשפה המשמשים עבור לוחות שנה של CJK, המוגדרים בתקן ECMA-376 18.8.30. תא שמזהה עיצוב המספר שלו נופל באחד מהטווחים הללו הוא תא תאריך או שעה.

מזהים מובנים מכסים את המקרים הנפוצים אך לא מותאמים אישית. כאשר חוברת עבודה מגדירה קוד עיצוב משלה, נניח סדר לא סטנדרטי או שם חודש מקומי, המזהה הוא מעל הטווח המובנה ומצביע לתוך טבלת עיצובי המספרים של חוברת העבודה. עבור אלה, זיהוי תאריך פירושו קריאת מחרוזת קוד העיצוב וחיפוש סימני תאריך (date tokens).‏ HotXLS מקפל את שתי הבדיקות הללו להיגד פנימי אחד, XlsxNumFmtIsDate, המחזיר true מיידית עבור טווחי התאריכים המובנים ואחרת מנתח את קוד העיצוב המותאם אישית דרך XlsxFormatCodeIsDate. הצד הציבורי של זה הוא מחרוזת ה-NumberFormat של התא וה-NumberFormatIndex שלו, הנותנים לך הן את קוד העיצוב שנפתר והן את המזהה לבדיקה.

מדוע מנתח העיצוב אינו יכול פשוט לסרוק d ו-m

ניתוח קוד עיצוב עבור סימני תאריך נראה פשוט עד שאתה נזכר מה עוד חי בעיצוב מספר. חיפוש נאיבי של האותיות המאייתות תאריכים – ה-d,‏ m,‏ y,‏ h,‏ s של יום, חודש, שנה, שעה ושנייה – יפספס בשני מבנים שאינם סימני תאריך כלל.

הראשון הוא מחרוזת טקסט מילולית מצוטטת. עיצוב מספר יכול להטמיע טקסט מילולי במירכאות כפולות, כך שעיצוב פיננסי כמו #,##0 "MM" מצרף את התווים M ו-M למספר ללא כל משמעות זמנית. סורק שיספור את האותיות בתוך המירכאות כסימני חודש יסמן בטעות את עיצוב המטבע הזה כתאריך. השני הוא מקטע הסוגריים. עיצובי מספרים נושאים הנחיות בסוגריים מרובעים, שמות צבעים כגון [Red], תנאי השוואה כגון [>1000], תגי שפה, ומסמני זמן שחלף [h] ו-[mm]. חלק מתוכן הסוגריים מחזיק אותיות תאריך וחלק לא, והתייחסות לטקסט בסוגריים כמו לגוף העיצוב מובילה הן לתוצאות חיוביות שגויות והן למקרים שהוחמצו.

המנתח הנכון עובר על קוד העיצוב תו אחר תו, עוקב אם הוא בתוך מחרוזת מילולית מצוטטת וכמה עמוק הוא בתוך סוגריים מקוננים, והוא גם מכבד את תו ה-backslash שמצטט תו בודד אחריו. רק אות תאריך לא מוצגת שנמצאת מחוץ לכל מחרוזת מילולית ומחוץ לכל מקטע סוגריים נחשבת כסימן תאריך אמיתי. זה בדיוק האופן שבו XlsxFormatCodeIsDate סורק: מירכאות הופכות מצב בתוך-מחרוזת שמבטל זיהוי סימנים עד למירכאות הסוגרות, backslash מדלג על התו הבא, ומונה עומק סוגריים מבטל זיהוי בתוך ריצות [...]. השכר הוא ש-#,##0 "MM" נקרא בצורה נכונה כעיצוב מספר, בעוד קוד מותאם אישית תמציתי המכיל רק m או d מחוץ למירכאות עדיין מזוהה נכון כתאריך.

קריאת תאריכים מתוך קובצי צד שלישי

כל מה שלעיל מתכנס לזרימת עבודה אחת: הפיכת מספר שאפליקציה אחרת כתבה בחזרה לתאריך שבו תוכל לבטוח. המספר הסידורי נותן לך את ספירת הימים, דגל ה-Date1904 של חוברת העבודה אומר לך מאיזה עידן ספירה זו נמדדת, ועיצוב המספר או קוד העיצוב של התא הוא הראיה היחידה לכך שהמספר נועד כתאריך מלכתחילה. שמט כל אחד משלושת אלה ותקבל תשובה שגויה שנראית סבירה במקום שגיאה גלויה.

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cell: TXLSXCell;
  r: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('vendor-export.xlsx') <> 1 then
      raise Exception.Create('Cannot open export');

    // The 1904 flag is workbook-wide: read it once, apply it to
    // every serial the workbook hands back.
    if Book.Date1904 then
      Writeln('workbook uses the 1904 date system')
    else
      Writeln('workbook uses the 1900 date system');

    Sheet := Book.Sheets[0];
    for r := 1 to 10 do
    begin
      Cell := Sheet.Cells[r, 1];
      // A date is only a date when its format says so; the same numeric
      // value with a plain format is just a quantity.
      Writeln(Format('row %d  value=%s  numFmt=%d  code="%s"',
        [r, VarToStr(Cell.Value), Cell.NumberFormatIndex, Cell.NumberFormat]));
    end;
  finally
    Book.Free;
  end;
end;

לצד ה-BIFF הישן יש מלכודת אחת נוספת ששווה להזכיר. בזרם .xls ישן יותר, ריצה של תאים מספריים סמוכים יכולה להיארז ברשומת רב-תא בודדת, ה-MULRK, השומרת מספר ערכים עם הפניות העיצוב שלהם במבנה אחד. תאי תאריך השמורים בצורה זו אינם פחות תאריכים בשל היותם ארוזים, כך שאותה בדיקת מזהה עיצוב חייבת להגיע לתוך רשומת הרב-תא ולהחיל לכל תא, והיסט 1904 עדיין שולט בכל מספר סידורי שהיא מניבה. קורא שבודק רק רשומות מספרים עצמאיות, ומדלג על הארוזות, יהפוך בשקט עמודה של תאריכים לעמודה של שלמים.

מיפוי מספרים סידוריים ל-TDateTime בפועל

ברגע שבדיקת העיצוב מאשרת תאריך ודגל ה-Date1904 ידוע, ההמרה היא מכנית. ערך ש-HotXLS כבר מוסר בחזרה כ-varDate הוא TDateTime שבו תוכל להשתמש ישירות. ערך שמגיע כ-Double פשוט, מה שקורה כאשר המקור כתב מספר סידורי ללא עיצוב תאריך מוכר, מומר על ידי קריאתו כספירת ימים על ציר 1900, ועבור חוברת עבודה של 1904, הפחתת היסט 1462 הימים תחילה כך שהעידנים יתיישרו. בכיוון ההפוך, הקצאת TDateTime לתא שומרת את המספר הסידורי המבוסס על 1900, ו-HotXLS מחיל את אותו היסט של 1462 ימים בשמירה כאשר Date1904 של חוברת העבודה מוגדר, כך שהקובץ שנשמר מציג את התאריך שהתכוונת אליו במקום תאריך הרחוק בארבע שנים.

הגדר את הדגל במכוון כאשר אתה מייצר חוברת עבודה. ברירת המחדל משאירה את Date1904 כ-false, מה שמתאים ל-Excel עבור Windows והוא כמעט תמיד מה שאתה רוצה; הגדר אותו כ-true רק כאשר אתה משחזר חוברת עבודה שמקורה ב-Mac או שמערכת במורד הזרם מצפה ספציפית לציר 1904. הכלל היחיד שמונע את כל מחלקת שגיאות ארבע השנים הוא עקביות: בחר את העידן פעם אחת לחוברת עבודה, כתוב כל תאריך תחתיו, וקרא כל מספר סידורי בחזרה תחת הדגל שהקובץ באמת נושא.

תאריכים הם עמודה אחת בסיפור רחב יותר על מה תא באמת מחזיק. שכבת המטא-נתונים השכנה, הכותרת והמחבר וחותמות הזמן שרוכבים לצד הגריד, מכוסה ב-מאמר שלנו על מטא-נתונים ומאפייני מסמך של חוברת עבודה, שבו אותם ערכי Created ו-Modified נשמרים כ-TDateTime עם אותה מוסכמת אי-הגדרה ששווה לאפס. כאשר תאריך הוא תוצאה של חישוב ולא ערך שמור, כללי ההערכה ב-מאמר שלנו על מנוע הנוסחאות ופונקציות מותאמות אישית קובעים את המספר הסידורי שהעיצוב מרנדר לאחר מכן. שניהם עובדים על אותו מודל תאריכים שנשלח בתוך HotXLS Component עבור Delphi ו-C++Builder, הקורא וכותב תאריכי XLS ו-XLSX ללא אוטומציית Excel.