Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

OpenOffice Calc User Guide
Previous Page Home Next Page

Date and time functions

Use these functions for inserting, editing and manipulating dates and times. OpenOffice.org 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 just 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.

To set the default date format used by Calc. go to Tools > Options > OpenOffice.org Calc > Calculate.

Caution

When entering dates, slashes or dashes used as date separators may be interpreted as arithmetic operators. To keep dates from being interpreted as parts of formulas, and thus returning erroneous results, always place them in quotation marks, for example, "12/08/52".



Table 14: 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 time 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. Only months are considered; days are not used for calculation. Months is the number of months.

*EOMONTH(start_date; months)

Returns the date of the last day of a month which 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. Date_1 is the start (earlier) date. Date_2 is the end date. Type is one of two possible values, 0 (interval) or 1 (in calendar months). If Date_2 is an earlier date than Date_1, 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 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. 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 under OOo to calculate date entries.

TODAY()

Returns the current computer system date. The value is updated when your document recalculates. 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 = 0); 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 internal date 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 year as a number according to the internal calculation rules. Number shows the internal 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(tart_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.

OpenOffice Calc User Guide
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire