IBM Lotus Symphony


Date and Time Functions

These spreadsheet functions are used for inserting and editing dates and times. The functions are: DATE, DATEVALUE, DAY, DAYS, DAYS360, EASTERSUNDAY, EDATE, EOMONTH, HOUR, MINUTE, MONTH, NETWORKDAYS, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WEEKNUM, WEEKNUM_ADD, WORKDAY, YEAR, YEAR2, YEARFRAC.

Note Icon IBM Lotus Symphony 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 Cells . The Numbers tab page contains the functions for defining the number format.
Note Icon When entering dates, slashes or dashes used as date separators may be interpreted as arithmetic operators. Therefore, dates entered in this format are not always recognized as dates and result in erroneous calculations. To keep dates from being interpreted as parts of formulas, place them in quotation marks, for example, "07/20/54".

WORKDAY

The result is 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 .

Syntax

WORKDAY (Start date;Days;Holidays)

Start date : the date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation.

Days : the number of workdays. Positive value for a result after the start date, negative value for a result before the start date.

Holidays : list of optional holidays. These are non-working days. Enter a cell range in which the holidays are listed individually.

Example

What date comes 17 workdays after 1 December 2001? Enter the start date "12/1/2001" in C3 and the number of workdays in D3. Cells F3 to J3 contain the following Christmas and New Year holidays: "12/24/2001", "12/25/2001", "12/26/2001", "12/31/2001", "1/1/2002".

=WORKDAY(C3;D3;F3;J3) returns 12/28/2001. Format the serial date number as a date.

YEARFRAC

The result is a number between 0 and 1, representing the fraction of a year between Start date and End date .

Syntax

YEARFRAC (Start date;End date;Basis)

Start date and end date : two date values.

Basis : is chosen from a list of options and indicates how the year is to be calculated.

Basis Calculation
0 or missing US method (NASD), 12 months of 30 days each
1 Exact number of days in months, exact number of days in year
2 Exact number of days in month, year has 360 days
3 Exact number of days in month, year has 365 days
4 European method, 12 months of 30 days each

Example

What fraction of the year 2001 lies between 1.1.2002 and 7.1.2001?

=YEARFRAC("1.1.2002"; "7.1.2002";1) returns 0.495890.

DATE

This function converts a date written as year, month, day to an internal serial number 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 the 0 number format, which displays the internal serial number of the date as a number.

Syntax

DATE(year; month; day)

Year is an integer between 1583 and 9956 or 0 and 99.

Month is an integer between 1 and 12 indicating the month.

Day is a number between 1 and 31 indicating the day of the month.

If the values for month and day are higher, they are carried over to the next digit. If you enter =DATE(00;12;31) the result will be "12/31/00." If, on the other hand, you enter =DATE(00;13;31) the result will be "1/31/01."

You can enter dates directly into the DATE function either as arguments, or range references.

Example

DATE("00;1;1") yields 1/1/00

See also the following functions:

DATEVALUE, TODAY, YEAR, NOW, MONTH, DAY, TIMEVALUE.

DATEVALUE

DATEVALUE returns the internal date number for text in quotes having a possible date format.

The internal number is returned as a natural number, resulting from the dates system used by IBM® Lotus® Symphony™ to calculate dates.

Syntax

DATEVALUE("Text")

Text is a valid date expression and must be entered with quotation marks.

Example

DATEVALUE("7/20/54") yields 19925

See also the following functions:

TODAY, NOW, TIMEVALUE.

EDATE

The result is a date which is a number of Months away from the Start date . Only months are considered; days are not used for calculation.

Syntax

EDATE(Start date;Months)

Start date : a date.

Months : the number of months.

Example

What date is one month prior to 3.31.2001?

=EDATE("3.31.2001";-1) returns 2.28.2001.

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.

See also the following functions:

DATE, NOW, DAY.

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.

Examples

Year(1) returns 1899

YEAR(2) returns 1900

YEAR(33333.33) returns 1991

See also the following functions:

DAY, HOUR, MINUTE, MONTH, NOW, SECOND, TODAY, WEEKDAY.

YEAR2

Extracts the year, an integer from 0 (the year 1900) through 8099 (the year 9999), from date-number.

Syntax

YEAR2(Date-Number;[Type])

Date-number is an integer, or the address or name of a cell that contains an integer, from 1 (January 1, 1900) through 2958465 (December 31, 9999).

Type is the number 0 or 1. If type is 1, YEAR2 returns the year in four-digit form. If type is 0 or omitted, YEAR2 returns the offset of the year from 1900 (for example, YEAR2 returns 123 to represent the year 2023).

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()

Example

=NOW()-A1 returns the difference between the date in A1 and now. Format the result as a number.

See also the following functions:

DAY, HOUR, MINUTE, MONTH, WEEKDAY, DATE, YEAR.

WEEKNUM

WEEKNUM calculates the week number of the year for the internal date value.

Syntax

WEEKNUM(number; mode)

Number is the internal date number.

Mode sets the start of the week and the calculation type.

  1 = Sunday
  2 = Monday

Examples

WEEKNUM("1/1/95";1) returns 1 (1/1/95 was a Sunday)

WEEKNUM("1/1/95";2) returns 52. If the week starts on Monday, Sunday belongs to the last week of the previous year.

WEEKNUM_ADD

The result indicates the number of the calendar week for a Date .

Syntax

WEEKNUM_ADD(Date;Return type)

Date : the date within the calendar week.

Return type : 1 for week beginning on a Sunday, 2 for week beginning on a Monday.

Example

In which week number does 12.24.2001 fall?

=WEEKNUM_ADD("24.12.2001";1) returns 52.

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.

Examples

MINUTE(8.999) returns 58

MINUTE(8.9999) returns 59

MINUTE(NOW()) returns the current minute value.

See also the following functions:

DAY, HOUR, MONTH, NOW, SECOND, WEEKDAY, YEAR.

MONTH

Returns the month for the given date value. The month is returned as an integer between 1 and 12.

Syntax

MONTH(Number)

Number , as a time value, is a decimal for which the month is to be returned.

Examples

MONTH(NOW()) returns the current month

MONTH(C4) returns 7 if contents of C4 = 7/7/00.

See also the following functions:

DAY, HOUR, MINUTE, NOW, WEEKDAY, YEAR.

EOMONTH

Returns the date of the last day of a month which falls Months away from the Start date .

Syntax

EOMONTH (Start date; Months)

Start date : calculated from this point onwards.

Months : the number of months before (negative) or after (positive) the Start Date.

Example

What is the last day of the month that falls 6 months after September 14 2001?

=EOMONTH("9.14.2001";6) returns 3.31.2002.

NETWORKDAYS

Returns the number of workdays between Start date and End date . Holidays can be deducted.

Syntax

NETWORKDAYS (Start date;End date;Holidays)

Start date : the date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation.

End date : the date up until when the calculation is carried out. If the end date is a workday, the day is included in the calculation.

Holidays : optional list of holidays. These are non-working days. Enter a cell range in which the holidays are listed individually.

Example

How many workdays fall between 12/15/2001 and 1/15/2002? The start date is located in C3 and the end date in D3. Cells F3 to J3 contain the following Christmas and New Year holidays: "12/24/2001", "12/25/2001", "12/26/2001", "12/31/2001", "1/1/2002".

=NETWORKDAYS(C3;D3;F3:J3) returns 17 workdays.

EASTERSUNDAY

Returns the date of Easter Sunday for the entered year. Year is an integer between 1583 and 9956 or 0 and 99. You can also calculate other holidays by simple addition with this date.

Easter Monday = EASTERSUNDAY() + 1

Good Friday = EASTERSUNDAY() - 2

Pentecost Sunday = EASTERSUNDAY() + 49

Pentecost Monday = EASTERSUNDAY() + 50

Examples

EASTERSUNDAY(2000) returns 4/23/00

EASTERSUNDAY(2000)+49 returns the internal serial number 36688. If you use the MMDDYY date format, the result is 06/11/00.

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.

Examples

SECOND(NOW()) returns the current second

SECOND(C4) returns 17 if contents of C4 = 12:20:17.

See also the following functions:

DATE, DAY, HOUR, MINUTE, MONTH, , NOW, WEEKDAY, YEAR.

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.

Examples

HOUR(NOW()) returns the current hour

HOUR(C4) returns 17 if the contents of C4 = 17:20:00.

See also the following functions:

DAY, MINUTE, MONTH, NOW, WEEKDAY, YEAR.

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 , as a time value, is a decimal, for which the day is to be returned.

Examples

DAY(1) returns 31 (since Lotus Symphony starts counting at zero from December 30, 1899)

DAY(NOW()) returns the current day.

DAY(C4) returns 5 if the contents of C4 = 8/5/1901.

See also the following functions:

HOUR, MINUTE, MONTH, NOW, SECOND, TODAY, WEEKDAY, YEAR.

DAYS

Calculates the difference between two date values. The result is an integer and returns the number of days between the two days.

Syntax

DAYS(Date_2;Date_1)

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.

Examples

DAYS("1/1/2010"; NOW()) returns the number of days from today until January 1, 2010.

DAYS("10/10/1990";"10/10/1980") returns 3652.

DAYS360

Returns the difference between two dates based on the 360 day year used in interest calculations. The result is an integer.

Syntax

DAYS360(Date_1;Date_2;Type)

If Date_2 is earlier than Date_1, the function will return a negative number.

The optional argument Type determines the type of difference calculation. If Type = 0 or if the argument is missing, the US method (NASD, National Association of Securities Dealers) is used. If Type <> 0, the European method is used.

Examples

DAYS360("1/1/2000";NOW()) returns the number of interest days from January 1, 2000 until today.

See also the following functions:

DAY

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. If type=2, numbering begins at Monday=1; and if type=3 numbering begins at Monday=0.

Syntax

WEEKDAY(Number; Type)

Number , as a date value, is a decimal for which the weekday is to be returned.

Type determines the type of calculation. For Type =1, the weekdays are counted starting from Sunday (this is the default even when the Type parameter is missing). For Type =2, the weekdays are counted starting from Monday =1. For Type = 3, the weekdays are counted starting from Monday = 0.

Examples

WEEKDAY("6/14/2000") 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("7/24/1996";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("7/24/1996";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(NOW()) returns the number of the current day.

To obtain a function indicating whether a day in A1 is a Business day, use the IF and WEEKDAY functions as follows: IF(WEEKDAY(A1;2)<6;"Business day";"Weekend")

See also the following functions:

DAY, NOW, TODAY.

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 .

Examples

TIME("0;0;0") returns 00:00:00

TIME("4;20;4") returns 04:20:04

See also the following functions:

HOUR, MINUTE, NOW, SECOND.

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 Lotus Symphony to calculate date entries.

Syntax

TIMEVALUE("Text")

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

Examples

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

TIMEVALUE("24:00") returns 1. If you use the HH:MM:SS time format, the value is 00:00:00.

See also the following functions:

DATEVALUE, HOUR, MINUTE, NOW, SECOND, TIME .


Product Feedback | Additional Documentation | Trademarks