1. Libreoffice Index Function

OpenOffice.org Basic supports several Visual Basic compatible statements and functions to perform date and time calculations. The functions are DateSerial, DateValue.

In this tutorial I will show how to do basic date processing in basic via LibreOffice CALC cells. In basic, Date is a datatype used to store date and time values. By default, it holds Jan 1 year 0001 midnight. We will pick any date and process it in different ways. Add days and months to a date Declaring a date variable can be done using below code: Dim mydate As Date Once declared, we will use DateValue function. This function helps to convert the text formatted date to a date format. We will put this date in a LibreOffice Calc cell.

Mydate = DateValue('Nov 20, 2014') mycell = ThisComponent.Sheets(0).getCellbyPosition(1,1) mycell.String = mydate To add 3 days to the above date, use below ccivbDateAdd/ccivb function. This function takes first argument as which part of the date needs to be increased. If you pass as “d” as in day, the date would be increased on day.

If you use “m”, it would increase the date as months. Increase the date by 3 days: mycell = ThisComponent.Sheets(0).getCellbyPosition(1,3) mycell.String = DateAdd('d', 3, mydate) Increase the date by 3 months: mycell = ThisComponent.Sheets(0).getCellbyPosition(1,5) mycell.String = DateAdd('m', 3, mydate) Formatting a Date Formatting a date always necesary for various calculations, macro processings especially when you need to extract the day, month, year, Weekday etc. Here are various ways of formatting a date.

Libreoffice Index Function

Each can be used with a valid date as argument.

These spreadsheet functions are used for inserting and editing dates and times. Insert - Function - Category Date&Time The functions whose names end with ADD or EXCEL2003 return the same results as the corresponding Microsoft Excel 2003 functions without the suffix. Use the functions without suffix to get results based on international standards.

LibreOffice internally handles a date/time value as a numerical value. If you assign the numbering format 'Number' to a date or time value, it is converted to a number. For example, 12:00 PM, converts to 36526.5. The value preceding the decimal point corresponds to the date; the value following the decimal point corresponds to the time.

If you do not want to see this type of numerical date or time representation, change the number format (date or time) accordingly. To do this, select the cell containing the date or time value, call its context menu and select Format Cells. The Numbers tab page contains the functions for defining the number format. Contents.

Date base for day zero Dates are calculated as offsets from a starting day zero. You can set the day zero to be one of the following: Date base Use ' (default) ' (used in former StarCalc 1.0) ' (used in Apple software) Choose Tools - Options' - LibreOffice Calc - Calculate' to select the date base.

When you copy and paste cells containing date values between different spreadsheets, both spreadsheet documents must be set to the same date base. If date bases differ, the displayed date values will change! Two digits years In Tools - Options' - LibreOffice - General' you find the area Year (two digits). This sets the period for which two-digit information applies.

Note that changes made here have an effect on some of the following functions. When entering dates as part of formulas, slashes or dashes used as date separators are interpreted as arithmetic operators.

Therefore, dates entered in this format are not recognized as dates and result in erroneous calculations. 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. Functions Related Topics.

This page was last edited 22:03:23, 2016-12-28 by LibreOffice Help user. Content is available under the GNU Lesser General Public License (LGPLv3), unless otherwise specified, originally based on OpenOffice.org help. 'LibreOffice' and 'The Document Foundation' are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries.

Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our unless otherwise noted. Comet tonight 11 7 07.