Date & Time Functions

From b560ca36513ef209b23081dc5b971d30dd60e5f1 Mon Sep 17 00:00:00 2001 From: "Chandanathil P. Geevan" Date: Mon, 24 Oct 2016 01:05:12 -0200 Subject: tdf#80439 Help pages for (NET)WORKDAYS.INTL removed useless file replaced commas with semicolons in function expression Change-Id: I76ce8945d7f5d51f7b356dc8063db1b4623e905b Reviewed-on: https://gerrit.libreoffice.org/30217 Reviewed-by: Olivier Hallot Tested-by: Olivier Hallot --- AllLangHelp_scalc.mk | 3 + help3xsl/get_bookmark.sh | 14 +- source/text/scalc/01/04060102.xhp | 264 +++++++++++----------- source/text/scalc/01/common_func_workdaysintl.xhp | 163 +++++++++++++ source/text/scalc/01/func_networkdays.intl.xhp | 65 ++++++ source/text/scalc/01/func_networkdays.xhp | 6 + source/text/scalc/01/func_workday.xhp | 6 + source/text/scalc/01/func_workdays.intl.xhp | 66 ++++++ 8 files changed, 445 insertions(+), 142 deletions(-) create mode 100644 source/text/scalc/01/common_func_workdaysintl.xhp create mode 100644 source/text/scalc/01/func_networkdays.intl.xhp create mode 100644 source/text/scalc/01/func_workdays.intl.xhp diff --git a/AllLangHelp_scalc.mk b/AllLangHelp_scalc.mk index 4574dfc618..e9cb0837e6 100644 --- a/AllLangHelp_scalc.mk +++ b/AllLangHelp_scalc.mk @@ -220,7 +220,10 @@ $(eval $(call gb_AllLangHelp_add_helpfiles,scalc,\ helpcontent2/source/text/scalc/01/func_isoweeknum \ helpcontent2/source/text/scalc/01/func_minute \ helpcontent2/source/text/scalc/01/func_month \ + helpcontent2/source/text/scalc/01/common_func_workdaysintl \ helpcontent2/source/text/scalc/01/func_networkdays \ + helpcontent2/source/text/scalc/01/func_networkdays.intl \ + helpcontent2/source/text/scalc/01/func_workdays.intl \ helpcontent2/source/text/scalc/01/func_numbervalue \ helpcontent2/source/text/scalc/01/func_now \ helpcontent2/source/text/scalc/01/func_second \ diff --git a/help3xsl/get_bookmark.sh b/help3xsl/get_bookmark.sh index 2ae124c712..b8e7ee6618 100755 --- a/help3xsl/get_bookmark.sh +++ b/help3xsl/get_bookmark.sh @@ -1,11 +1,11 @@ #/bin/bash - +#*-- +#* This file is part of the LibreOffice project. +#* +#* This Source Code Form is subject to the terms of the Mozilla Public +#* License, v. 2.0. If a copy of the MPL was not distributed with this +#* file, You can obtain one at http://mozilla.org/MPL/2.0/. +#* stub1='

' stub2='' diff --git a/source/text/scalc/01/04060102.xhp b/source/text/scalc/01/04060102.xhp index 09f3bf76a6..47e8ce1dca 100644 --- a/source/text/scalc/01/04060102.xhp +++ b/source/text/scalc/01/04060102.xhp @@ -1,6 +1,5 @@ - - - +--> + - -Date & Time Functions -/text/scalc/01/04060102.xhp - + + Date & Time Functions + /text/scalc/01/04060102.xhp + + -date and time functions -functions; date & time -Function Wizard; date & time + + + date and time functions + functions; date & time + Function Wizard; date & time -Date & Time Functions -These spreadsheet functions are used for inserting and editing dates and times. - + + +Date & Time Functions +These spreadsheet functions are used for inserting and editing dates and times. +

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

-$[officename] 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, 01/01/2000 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. -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: +$[officename] 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, 01/01/2000 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. + +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 - - - - -'12/30/1899' - - -(default) - - - - -'01/01/1900' - - -(used in former StarCalc 1.0) - - - - -'01/01/1904' - - -(used in Apple software) - - + + + Date base + + + Use + + + + + '12/30/1899' + + + (default) + + + + + '01/01/1900' + + + (used in former StarCalc 1.0) + + + + + '01/01/1904' + + + (used in Apple software) + +

-Choose %PRODUCTNAME - PreferencesTools - Options - %PRODUCTNAME 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 %PRODUCTNAME - PreferencesTools - Options - $[officename] - 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 +Choose %PRODUCTNAME - Preferences +Tools - Options - %PRODUCTNAME 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 %PRODUCTNAME - Preferences +Tools - Options - $[officename] - 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 +

- - - +

+ +

- - - +

- + + \ No newline at end of file diff --git a/source/text/scalc/01/common_func_workdaysintl.xhp b/source/text/scalc/01/common_func_workdaysintl.xhp new file mode 100644 index 0000000000..cc515b8e7e --- /dev/null +++ b/source/text/scalc/01/common_func_workdaysintl.xhp @@ -0,0 +1,163 @@ + + + + + + + common workdays intl + /text/scalc/01/common_func_workdaysintl.xhp + + + + + + +

+Weekend is an optional parameter – a number or a string used to specify the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur. Weekend number values indicate the following weekend days: +Number 1 to 7 for two-day weekends and 11 to 17 for one-day weekends. + + + + + Number + + + Weekend + + + + + 1 or omitted + + + Saturday and Sunday + + + + + 2 + + + Sunday and Monday + + + + + 3 + + + Monday and Tuesday + + + + + 4 + + + Tuesday and Wednesday + + + + + 5 + + + Wednesday and Thursday + + + + + 6 + + + Thursday and Friday + + + + + 7 + + + Friday and Saturday + + + + + 11 + + + Sunday only + + + + + 12 + + + Monday only + + + + + 13 + + + Tuesday only + + + + + 14 + + + Wednesday only + + + + + 15 + + + Thursday only + + + + + 16 + + + Friday only + + + + + 17 + + + Saturday only + + +

+ +Weekend string provides another way to define the weekly non-working days. It must have seven (7) characters – zeros (0) for working day and ones (1) for non-working day. Each character represents a day of the week, starting with Monday. Only 1 and 0 are valid. “1111111” is an invalid string and should not be used. For example, the weekend string “0000011” defines Saturday and Sunday as non-working days. +Holidays is an optional list of dates that must be counted as non-working days. The list can be given in a cell range. +

+ + + \ No newline at end of file diff --git a/source/text/scalc/01/func_networkdays.intl.xhp b/source/text/scalc/01/func_networkdays.intl.xhp new file mode 100644 index 0000000000..11fbf61f38 --- /dev/null +++ b/source/text/scalc/01/func_networkdays.intl.xhp @@ -0,0 +1,65 @@ + + + + + + + NETWORKDAYS.INTL + /text/scalc/01/func_networkdays.intl.xhp + + + + + + +

+ + NETWORKDAYS.INTL function + + + + +NETWORKDAYS.INTL +Returns the number of workdays between a start date and an end date. There are options to define weekend days and holidays. The optional weekend parameter (or a string) can be used to define the weekend days (or the non-working days in each week). Also, optionally, the user can define a holiday list. The weekend days and user-defined holidays are not counted as working days. +

+ +Syntax +NETWORKDAYS.INTL(StartDate; EndDate; Weekend; Holidays) +StartDate is the date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation. +EndDate is the date up until when the calculation is carried out. If the end date is a workday, the day is included in the calculation. +

+ +Example +How many workdays fall between December 15, 2016 and January 14, 2017? Let the start date be located in C3 and the end date in D3. Cells F3 to J3 contain five (5) holidays for Christmas and New Year in date format: December 24, 2016; December 25, 2016; December 26, 2016; December 31, 2016; and January 1, 2017. +=NETWORKDAYS.INTL(C3;D3;;F3:J3) returns 21 workdays with default for weekend days. +=NETWORKDAYS.INTL(C3;D3;11;F3:J3) returns 24 workdays with Sunday only weekends. +Alternatively, use the weekend string “0000001” to define Sunday as the non-working day of every week. +=NETWORKDAYS.INTL(C3;D3;“0000001”;F3:J3) returns 24 workdays with Sunday only weekend. +The function can be used without the two optional parameters – weekday and holidays – by leaving them out: +=NETWORKDAYS.INTL(C3;D3) gives 22 working days. + +

+NETWORKDAYS +WORKDAYS.INTL +WORKDAYS +Date functions +

+ + + \ No newline at end of file diff --git a/source/text/scalc/01/func_networkdays.xhp b/source/text/scalc/01/func_networkdays.xhp index c56f573958..e5f4d201fa 100644 --- a/source/text/scalc/01/func_networkdays.xhp +++ b/source/text/scalc/01/func_networkdays.xhp @@ -48,6 +48,12 @@ =NETWORKDAYS(C3;D3;F3:J3) returns 17 workdays. How many workdays fall between September 12nd and 25th in 2016 if only Mondays, Tuesdays and Wednesdays are considered as workdays? =NETWORKDAYS(DATE(2016;9;12); DATE(2016;9;25); ; {1;0;0;0;1;1;1}) returns 6 workdays. +

+NETWORKDAYS.INTL +WORKDAYS.INTL +WORKDAYS +Date functions +

\ No newline at end of file diff --git a/source/text/scalc/01/func_workday.xhp b/source/text/scalc/01/func_workday.xhp index ac8f574d21..93be30a7e9 100644 --- a/source/text/scalc/01/func_workday.xhp +++ b/source/text/scalc/01/func_workday.xhp @@ -45,5 +45,11 @@ Example What date came 17 workdays after 1 December 2001? Enter the start date "2001-12-01" in C3 and the number of workdays in D3. Cells F3 to J3 contain the following Christmas and New Year holidays: "2001-12-24", "2001-12-25", "2001-12-26", "2001-12-31", "2002-01-01". =WORKDAY(C3;D3;F3:J3) returns 2001-12-28. Format the serial date number as a date, for example in the format YYYY-MM-DD.UFI: fixed #i30213# +

+NETWORKDAYS +NETWORKDAYS.INTL +WORKDAYS.INTL +Date functions +

diff --git a/source/text/scalc/01/func_workdays.intl.xhp b/source/text/scalc/01/func_workdays.intl.xhp new file mode 100644 index 0000000000..4f90642276 --- /dev/null +++ b/source/text/scalc/01/func_workdays.intl.xhp @@ -0,0 +1,66 @@ + + + + + + + WORKDAYS.INTL + /text/scalc/01/func_workdays.intl.xhp + + + + + + +

+ + +WORKDAYS.INTL +The result is a date number that can be formatted as a date. User can see the date of a day that is a certain number of workdays away from the start date (before or after). There are options to define weekend days and holidays. The optional weekend parameter (or a string) can be used to define the weekend days (or the non-working days in each week). Also, optionally, the user can define a holiday list. The weekend days and user-defined holidays are not counted as working days. +

+ +Syntax +WORKDAY.INTL(StartDate; Days; Weekend; Holidays) +StartDate is the date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation. This is required. +Days is the number of workdays. Positive value for a result after the start date, negative value for a result before the start date. +

+ +Example +What date comes 20 workdays after December 13, 2016? Enter the start date in C3 and the number of workdays in D3. +The weekend parameter (number) may be left blank or defined as 1 for default weekend (non-working days) – Saturday and Sunday. +Cells F3 to J3 contain five (5) holidays for Christmas and New Year in date format: December 24, 2016; December 25, 2016; December 26, 2016; December 31, 2016; and January 1, 2017. +=WORKDAY.INTL(C3;D3;;F3:J3) returns January 11, 2017 in the result cell, say D6 (use date format for the cell). +To define Friday and Saturday as weekend days, use the weekend parameter 7. +=WORKDAY.INTL(C3;D3;7;F3:J3) returns January 15, 2017 with weekend parameter 7. +To define Sunday only the weekend day, use the weekend parameter 11. +=WORKDAY.INTL(C3;D3;11;F3:J3) returns January 9, 2017. +Alternatively, use the weekend string “0000001” for Sunday only weekend. +=WORKDAY.INTL(C3;D3;“0000001”;F3:J3) returns January 9, 2017. +The function can be used without the two optional parameters – Weekday and Holidays – by leaving them out: +=WORKDAY.INTL(C3;D3) gives the result: January 10, 2017. + +

+NETWORKDAYS +NETWORKDAYS.INTL +WORKDAYS +Date functions +

+ + + \ No newline at end of file -- cgit v1.2.3