Libreoffice basic date functions

Date & Time Functions

1 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.

2 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.

3 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

4 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).

5 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).

6 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 “Interval”Return 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 years between Start date and End date.
“ym”Number of whole months when subtracting years from the difference of Start date and End date.
“md”Number of whole days when subtracting years and months from the difference of Start date and End date.
“yd”Number of whole days when subtracting years from the difference of 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.

8 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.

9 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.

10 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.

11 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.

12 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.

13 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.

TypeWeekday number returned
1 or omitted1 (Sunday) through 7 (Saturday). For compatibility with Microsoft Excel.
21 (Monday) through 7 (Sunday).
30 (Monday) through 6 (Sunday)
111 (Monday) through 7 (Sunday).
121 (Tuesday) through 7 (Monday).
131 (Wednesday) through 7 (Tuesday).
141 (Thursday) through 7 (Wednesday).
151 (Friday) through 7 (Thursday).
161 (Saturday) through 7 (Friday).
171 (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.

 14 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

15 TIMEVALUE

TIMEVALUE 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, TIMEVALUE only returns the fractional part of the conversion.

Syntax

TIMEVALUE(“Text”)

Text is a valid time expression and must be entered in quotation marks.

Example

=TIMEVALUE(“4PM”) returns 0.67. When formatting in time format HH:MM:SS, you then get 16:00:00.

=TIMEVALUE(“24:00”) returns 0. If you use the HH:MM:SS time format, the value is 00:00:00.

Leave a Comment

Your email address will not be published. Required fields are marked *

error: Content is protected !!