Date & Time Functions

TODAY

  • Returns the current computer system date. The value is updated when you reopen the document or modify the values of the document.
    Syntax
    TODAY()
    TODAY is a function without arguments.
Example

TODAY() returns the current computer system date.

NOW

  • Returns the computer system date and time. The value is updated when you recalculate the document or each time a cell value is modified.
    Syntax
    NOW()
    NOW is a function without arguments.
Example

=NOW() it returns the difference between the date in cell and now. Format the result as a number.

YEAR

  • Returns the year as a number according to the internal calculation rules.
    Syntax
    YEAR(Number)
    Number shows the internal date value for which the year is to be returned.
Example

=YEAR(1) returns 1899
=YEAR(2) returns 1900
=YEAR(33333.33) returns 1991

MONTH

  • Returns the month for the given date value. The month is returned as an integer between 1 and 12.
    Syntax
    MONTH(Number)
    Number is the internal date number.
Example

=MONTH(NOW()) returns the current month.
=MONTH(C4) returns 7 if you enter 2000-07-07 to cell C4 (that date value might get formatted differently after you press Enter).

DAY

  • Returns the day of given date value. The day is returned as an integer between 1 and 31. You can also enter a negative date/time value.
    Syntax
    DAY(Number)
    Number is the internal date number.
Example

=DAY(1) returns 31 (since LibreOffice starts counting at zero from December 30, 1899)
=DAY(NOW()) returns the current day.
=DAY(C4) returns 5 if you enter 1901-08-05 in cell C4 (the date value might get formatted differently after you press Enter).

DATE

  • This function calculates a date specified by year, month, day and displays it in the cell’s formatting. The default format of a cell containing the DATE function is the date format, but you can format the cells with any other number format.
    Syntax
    DATE(Year; Month; Day)
    Year is an integer between 1583 and 9957 or between 0 and 99.
    Month is an integer indicating the month.
    Day is an integer indicating the day of the month.
    If the values for month and day are out of bounds, they are carried over to the next digit. If you enter =DATE(00;12;31) the result will be 2000-12-31. If, on the other hand, you enter =DATE(00;13;31) the result will be 2001-01-31.
Example

=DATE(00;1;31) yields 1/31/00 if the cell format setting is MM/DD/YY.

7 DATEDIF

This function returns the number of whole days, months or years between Start date and End date.
Syntax
DATEDIF(Start date; End date; Interval)
Start date is the date from when the calculation is carried out.
End date is the date until the calculation is carried out. End date must be later, than Start date.
Interval is a string, accepted values are “d”, “m”, “y”, “ym”, “md” or “yd”.

Value for “Intervai” Retrun Value
“d” Number of whole days between start date and End date.
“m” Number of whole months between start date and End date.
“y” Number of whole year between start date and End date.
“ym” Number of whole months when subtracting years from the difference between start date and End date.
“md” Number of whole days when subtracting years and months from the difference between start date and End date.
“yd” Number of whole days when subtracting years from the difference between start date and End date.
Example

Birthday calculation. A man was born on 1974-04-17. Today is 2012-06-13.
=DATEDIF(“1974-04-17″;”2012-06-13″;”y”) yields 38.
=DATEDIF(“1974-04-17″;”2012-06-13″;”ym”) yields 1.
=DATEDIF(“1974-04-17″;”2012-06-13″;”md”) yields 27.
So he is 38 years, 1 month and 27 days old.
=DATEDIF(DATE(1974,4,17);”2012-06-13″;”m”) yields 457, he has been living for 457 months.
=DATEDIF(“1974-04-17″;”2012-06-13″;”d”) yields 13937, he has been living for 13937 days.
=DATEDIF(“1974-04-17”;DATE(2012;06;13);”yd”) yields 57, his birthday was 57 days ago.

ISOWEEKNUM

  • ISOWEEKNUM calculates the week number of the year for the internal date value.
    Syntax
    ISOWEEKNUM(Number)
    Number is the internal date number.
    Example

    =ISOWEEKNUM(DATE(1995;1;1)) returns 52. Week 1 starts on Monday, 1995-01-02.
    =ISOWEEKNUM(DATE(1999;1;1)) returns 53. Week 1 starts on Monday, 1999-01-04.

    MINUTE

    • Calculates the minute for an internal time value. The minute is returned as a number between 0 and 59.
      Syntax
      MINUTE(Number)
      Number, as a time value, is a decimal number where the number of the minute is to be returned.
    Example

    =MINUTE(8.999) returns 58
    =MINUTE(8.9999) returns 59
    =MINUTE(NOW()) returns the current minute value.

    SECOND

    • Returns the second for the given time value. The second is given as an integer between 0 and 59.
      Syntax
      SECOND(Number)
      Number, as a time value, is a decimal, for which the second is to be returned.
    Example

    =SECOND(NOW()) returns the current second
    =SECOND(C4) returns 17 if contents of C4 = 12:20:17.

    HOUR

    • Returns the hour for a given time value. The hour is returned as an integer between 0 and 23.
      Syntax
      HOUR(Number)
      Number, as a time value, is a decimal, for which the hour is to be returned.
    Example

    =HOUR(NOW()) returns the current hour
    =HOUR(C4) returns 17 if the contents of C4 = 17:20:00.

    DAYS

    • Calculates the difference between two date values. The result returns the number of days between the two days.
      Syntax
      DAYS(Date2; Date1)
      Date1 is the start date, Date2 is the end date. If Date2 is an earlier date than Date1 the result is a negative number.
    Example

    =DAYS(NOW();”2010-01-01″)) returns the number of days from January 1, 2010 until today.
    =DAYS(“1990-10-10″;”1980-10-10”) returns 3652 days.

    WEEKDAY

    Returns the day of the week for the given date value. The day is returned as an integer between 1 (Sunday) and 7 (Saturday) if no type or type=1 is specified. For other types, see the table below.
    Syntax
    WEEKDAY(Number; Type)
    Number, as a date value, is a decimal for which the weekday is to be returned.
    Type is optional and determines the type of calculation.

    Type Weekday number returned
    1 or omitted 1 (Sunday) through 7 (Saturday). For compatibility with Excel.
    2 1 (Monday) through 7 (Sunday).
    3 0 (Monday) through 6 (Sunday).
    11 1 (Monday) through 7 (Sunday).
    12 1 (Tuesday) through 7 (Monday).
    13 1 (Wednesday) through 7 (Tuesday).
    14 1 (Thursday) through 7 (Wednesday).
    15 1 (Friday) through 7 (Thursday).
    16 1 (Saturday) through 7 (Friday).
    17 1 (Sunday) through 7 (Saturday).
    Example

    =WEEKDAY(“2000-06-14”) returns 4 (the Type parameter is missing, therefore the standard count is used. The standard count starts with Sunday as day number 1. June 14, 2000 was a Wednesday and therefore day number 4).
    =WEEKDAY(“1996-07-24”;2) returns 3 (the Type parameter is 2, therefore Monday is day number 1. July 24, 1996 was a Wednesday and therefore day number 3).
    =WEEKDAY(“1996-07-24”;1) returns 4 (the Type parameter is 1, therefore Sunday is day number 1. July 24, 1996 was a Wednesday and therefore day number 4).
    =WEEKDAY(“2017-05-02”;14) returns 6 (the Type parameter is 14, therefore Thursday is day number 1. May 2, 2017 was a Tuesday and therefore day number 6)
    =WEEKDAY(NOW()) returns the number of the current day.

    TIME

    • TIME returns the current time value from values for hours, minutes and seconds. This function can be used to convert a time based on these three elements to a decimal time value.
      Syntax
      TIME(Hour; Minute; Second)
    • Use an integer to set the Hour.
    • Use an integer to set the Minute.
    • Use an integer to set the Second
    Example

    =TIME(0;0;0) returns 00:00:00
    =TIME(4;20;4) returns 04:20:04

    TIMDIPIKALUE

    • TIMDIPIKALUE returns the internal time number from a text enclosed by quotes and which may show a possible time entry format.
      The internal number indicated as a decimal is the result of the date system used under LibreOffice to calculate date entries.
      If the text string also includes a year, month, or day, TIMDIPIKALUE only returns the fractional part of the conversion.
      Syntax
      TIMDIPIKALUE(“Text”)
      Text is a valid time expression and must be entered in quotation marks.
    Example

    =TIMDIPIKALUE(“4PM”) returns 0.67. When formatting in time format HH:MM:SS, you then get 16:00:00.
    =TIMDIPIKALUE(“24:00”) returns 0. If you use the HH:MM:SS time format, the value is 00:00:00.

    Text Functions This section contains descriptions of the Text functions ...
    Array Functions This category contains the array functions.What is an ...
    Statistical Functions COUNT Counts how many numbers are in the ...
    Mathematical Functions Mathematical This category contains the Mathematical functions for ...
    LibreOffice Calc Logical Functions This category contains the Logical functions ...
    Date & Time Functions TODAY Returns the current computer system ...
    Database Function Parameters A B C D E 1 Name ...
    Information function in calc CELL Returns information on address, formatting ...