Date and Time Functions

Apache OpenOffice 4.1 Calc Guide

Appendix B — Description of Functions

< Previous Section

Next Section >

• Functions Available in Calc
  • Terminology: Numbers and Arguments
• Mathematical Functions
• Financial Analysis Functions
  • A Note About Dates
  • A Note About Interest Rates
• Statistical Analysis Functions
• Date and Time Functions
• Logical Functions
• Informational Functions
• Database Functions
• Array Functions
• Spreadsheet Functions
• Text Functions
• Converting Decimal, Binary, Octal, Hexadecimal
• Complex Number Functions

< Previous Page

Next Page >

Date and Time Functions

Use these functions to insert, edit, and manipulate dates and times. OpenOffice handles and computes a date/time value as a number. When you assign the number format “Number” to a date or time value, it is displayed as a number. For example, 01/01/2000 12:00 PM converts to 36526.5. This is simply a matter of formatting; the actual value is always stored and manipulated as a number. To see the date or time displayed in a standard format, change the number format (date or time) accordingly.

Table 40: Data and time functions

Syntax	Description
DATE(year; month; day)	Converts a date written as year, month, day to an internal serial number and displays it in the cell’s formatting. Year is an integer between 1583 and 9956 or 0 and 99. Month is an integer between 1 and 12. Day is an integer between 1 and 31.
DATEVALUE("Text")	Returns the internal date number for text in quotes. Text is a valid date expression and must be entered with quotation marks.
DAY(number)	Returns the day, as an integer, of the given date value. A negative date/time value can be entered. Number is a date value.
DAYS(date_2; date_1)	Calculates the difference, in days, between two date values. Date_1 is the start date. Date_2 is the end date. If Date_2 is an earlier date than Date_1, the result is a negative number.
DAYS360(date_1; date_2; type)	Returns the difference between two dates based on the 360-day year used in interest calculations. If Date_2 is earlier than Date_1, the function will return a negative number. Type (optional) determines the type of difference calculation: the US method (0) or the European method (≠0).
DAYSINMONTH(date)	Calculates the number of days in the month of the given date.
DAYSINYEAR(date)	Calculates the number of days in the year of the given date.
EASTERSUNDAY(integer)	Returns the date of Easter Sunday for the entered year. Year is an integer between 1583 and 9956 or 0 and 99.
EDATE(start_date; months)	The result is a date a number of Months away from the given Start_date. Months is the number of months. For example, =EDATE("2020-12-31";2) returns February 28, 2021.
EOMONTH(start_date; months)	Returns the date of the last day of a month that falls Months away from the given Start_date. Months is the number of months before (negative) or after (positive) the start date.
HOUR(number)	Returns the hour, as an integer, for the given time value. Number is a time value.
ISLEAPYEAR(date)	Determines whether a given date falls within a leap year. Returns either 1 (TRUE) or 0 (FALSE).
MINUTE(number)	Returns the minute, as an integer, for the given time value. Number is a time value.
MONTH(number)	Returns the month, as an integer, for the given date value. Number is a time value.
MONTHS(start_date; end_date; type)	Calculates the difference, in months, between two date values. Type is one of two possible values, 0 (interval) or 1 (in calendar months). If end_date is an earlier date than start_date, the result is a negative number.
NETWORKDAYS(start _date; end_date; holidays)	Returns the number of workdays between start_date and end_date. Holidays can be deducted. Start_date is the date from which the calculation is carried out. End_date is the date up to which the calculation is carried out. If the start or end date is a workday, the day is included in the calculation. Holidays (optional) is a list of holidays. Enter a cell range in which the holidays are listed individually.
NOW()	Returns the computer system date and time. The value is updated when your document recalculates. NOW is a function without arguments.
SECOND(number)	Returns the second, as an integer, for the given time value. Number is a time value.
TIME(hour; minute; second)	Returns a 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. Hour, minute, and second must all be integers.
TIMEVALUE(text)	Returns the internal time number from a text enclosed by quotes in a time entry format. The internal number indicated as a decimal is the result of the date system used in OpenOffice to calculate date entries.
TODAY()	Returns the current computer system date. The value is updated when your document recalculates. As the empty parentheses indicate, TODAY is a function without arguments.
WEEKDAY(number; type)	Returns the day of the week for the given number (date value). The day is returned as an integer based on the type. Type determines the type of calculation: type = 1 (default), the weekdays are counted starting from Sunday (Monday = 2); type = 2, the weekdays are counted starting from Monday (Monday = 1); type = 3, the weekdays are counted starting from Monday (Monday = 0).
WEEKNUM(number; mode)	Calculates the number of the calendar week of the year for the date argument number. Mode sets the start of the week and the calculation type: 1 = Sunday, 2 = Monday.
WEEKNUM_ADD(date; return_type)	Calculates the calendar week of the year for a Date. Date is the date within the calendar week. Return_type sets the start of the week and the calculation type: 1 = Sunday, 2 = Monday.
WEEKS(start_date; end_date; type)	Calculates the difference in weeks between two dates, start_date and end_date. Type is one of two possible values, 0 (interval) or 1 (in numbers of weeks).
WEEKSINYEAR(date)	Calculates the number of weeks in a year until a certain date. A week that spans two years is added to the year in which most days of that week occur.
WORKDAY(start_date; days; holidays)	Returns a date number that can be formatted as a date. You then see the date of a day that is a certain number of Workdays away from the start_date. Holidays (optional) is a list of holidays. Enter a cell range in which the holidays are listed individually.
YEAR(number)	Returns the value of the year portion of a date. Number shows the date value for which the year is to be returned.
YEARFRAC(start_date; end_date; basis)	Returns a number between 0 and 1, representing the fraction of a year between start_date and end_date. Start_date and end_date are two date values. Basis is chosen from a list of options and indicates how the year is to be calculated.
YEARS(start_date; end_date; type)	Calculates the difference in years between two dates: the start_date and the end_date. Type calculates the type of difference.