Talk:Documentation/Calc Functions/DATE

    From The Document Foundation Wiki

    SF Comments

    (1) Summary. Suggest replacing with the following - “Calculates a date-time sequence number for given year, month, and day values.”
    (2) Summary. Suggest including the following as a tip – “To keep dates from being interpreted as parts of formulas use the DATE function, for example, DATE(1954;7;20), or place the date in quotation marks and use the ISO 8601 notation, for example, "1954-07-20". Avoid using locale dependent date formats such as "07/20/54", the calculation may produce errors if the document is loaded under different locale settings.”.
    (3) Returns. Suggest replacing with the following - “Returns a real number which is the date-time sequence number for the given arguments. The returned value is formatted as a date by default, but you can apply other formats as needed.”
    (4) Arguments, Year. Suggest replacing with the following - “Year is a non-negative integer value or a reference to a cell containing that value, which is the year to be used. Values in the range 0 to 99 are converted to four-digit years in accordance with the Year (Two Digits) setting on the Tools > Options > LibreOffice > General dialog. However, to avoid confusion it is better to always use four-digit years.”
    (5) Arguments, Month. Suggest replacing with the following - “Month is an integer value or a reference to a cell containing that value, which is the month to be used. Both positive and negative values are accepted; values outside the normal range of 1 to 12 result in carry over to the calculated year value.”
    (6) Arguments, Day. Suggest replacing with the following - “Day is an integer value or a reference to a cell containing that value, which is the day to be used. Both positive and negative values are accepted; values outside the normal range (1 to 31, dependent on month) result in carry over to the calculated month value.”
    (7) Arguments, 1st bullet. Delete – covered by above changes.
    (8) Arguments, 2nd bullet. Suggest replacing with the following – “If any of Year, Month, or Day is a non-integer value, then the function truncates it to an integer”.
    (9) Arguments, insert new bullet. “If no value is provided for the Year argument (for example, =DATE(,1,1)), then Calc reports a missing variable error (Err:511). If no value is provided for either the Month or the Day argument (for example, =DATE(2021,,)), then Calc applies a default value of 0.”
    (10) Arguments, insert new bullet. “If the value of the Year argument is negative, then Calc reports an invalid argument error”.
    (11) Arguments, insert new bullet. “If DATE calculates a date-time sequence number that corresponds to a date before 1582-10-15, Calc reports a #VALUE! error. The Gregorian calendar was introduced in October 1582.”
    (12) Arguments, insert new bullet. “ODF 1.2 requires that DATE accept year values up to and including 9956. Calc’s implementation will process year values larger than this limit.”

    Note: In my opinion the Additional details section needs re-drafting. It contains too many pinned items and the result of highlighting so much material is much confusion. The following comments propose revised text; please assume that these comments replace all material currently in this section.

    (13) Additional details, new paragraph. “The description on this page uses the international standard ISO 8601 date format for clarity because this does not depend on locale; for example, 26th December 2020 is shown as 2020-12-26. The default date format on your computer may be different.”
    (14) Additional details, new paragraph. “Calc handles date/time values as numbers, known as date-time sequence numbers. For example, 12:00 PM on 2000-01-01, converts to 36526.5 (assuming that day zero is 1899-12-30). The value preceding the decimal point corresponds to the date; the value following the decimal point corresponds to the time. Times are represented as a fraction of a day - for example 0.5 is half a day, or 12 hours, or 12 noon; 0.25 is a quarter of a day, or 6 am.”
    (15) Additional details, new paragraph. “Dates are calculated as offsets from a starting day zero. Calc’s default day zero is 1899-12-30. You can optionally set the day zero to either 1900-01-01 or 1904-01-01 should it be required for compatibility with spreadsheets produced using other office suites. These options are available in the Date area of the Tools > Options > LibreOffice Calc > Calculate dialog. When you copy and paste cells containing date values between different spreadsheets, both documents must be set to the same date base. If date bases differ, the displayed date values will change.”
    (16) Examples, general. Please update all examples so that dates are shown in ISO 8601 format.
    (17) Examples, general. Please review the previous comments and include some extra examples to demonstrate some of the conditions that I have highlighted.

    --Stevefanning (talk) 2021-02-10T20:03:52 (UTC) Steve Fanning - My comments listed above addressed on 2021-02-10.