IBM Lotus Symphony
|
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.
![]() |
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. |
![]() |
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". |
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 .
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.
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.
The result is a number between 0 and 1, representing the fraction of a year between Start date and End date .
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 |
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.
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.
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.
DATE("00;1;1") yields 1/1/00
See also the following functions:
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.
DATEVALUE("Text")
Text is a valid date expression and must be entered with quotation marks.
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.
EDATE(Start date;Months)
Start date : a date.
Months : the number of months.
What date is one month prior to 3.31.2001?
=EDATE("3.31.2001";-1) returns 2.28.2001.
Returns the current computer system date. The value is updated when you reopen the document or modify the values of the document.
TODAY()
Today is a function without arguments.
TODAY() returns the current computer system date.
See also the following functions:
YEAR(number)
Number shows the internal date value for which the year is to be returned.
Year(1) returns 1899
YEAR(2) returns 1900
YEAR(33333.33) returns 1991
See also the following functions:
Extracts the year, an integer from 0 (the year 1900) through 8099 (the year 9999), from date-number.
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).
Returns the computer system date and time. The value is updated when you recalculate the document or each time a cell value is modified.
NOW()
=NOW()-A1 returns the difference between the date in A1 and now. Format the result as a number.
See also the following functions:
WEEKNUM(number; mode)
Number is the internal date number.
Mode sets the start of the week and the calculation type.
1 = Sunday | |
2 = Monday |
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(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.
In which week number does 12.24.2001 fall?
=WEEKNUM_ADD("24.12.2001";1) returns 52.
Calculates the minute for an internal time value. The minute is returned as a number between 0 and 59.
MINUTE(number)
Number , as a time value, is a decimal number where the number of the minute is to be returned.
MINUTE(8.999) returns 58
MINUTE(8.9999) returns 59
MINUTE(NOW()) returns the current minute value.
See also the following functions:
Returns the month for the given date value. The month is returned as an integer between 1 and 12.
MONTH(Number)
Number , as a time value, is a decimal for which the month is to be returned.
MONTH(NOW()) returns the current month
MONTH(C4) returns 7 if contents of C4 = 7/7/00.
See also the following functions:
EOMONTH (Start date; Months)
Start date : calculated from this point onwards.
Months : the number of months before (negative) or after (positive) the Start Date.
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.
Returns the number of workdays between Start date and End date . Holidays can be deducted.
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.
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.
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
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.
Returns the second for the given time value. The second is given as an integer between 0 and 59.
SECOND(Number)
Number , as a time value, is a decimal, for which the second is to be returned.
SECOND(NOW()) returns the current second
SECOND(C4) returns 17 if contents of C4 = 12:20:17.
See also the following functions:
HOUR(Number)
Number , as a time value, is a decimal, for which the hour is to be returned.
HOUR(NOW()) returns the current hour
HOUR(C4) returns 17 if the contents of C4 = 17:20:00.
See also the following functions:
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.
DAY(Number)
Number , as a time value, is a decimal, for which the day is to be returned.
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:
Calculates the difference between two date values. The result is an integer and returns the number of days between the two days.
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.
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.
Returns the difference between two dates based on the 360 day year used in interest calculations. The result is an integer.
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.
DAYS360("1/1/2000";NOW()) returns the number of interest days from January 1, 2000 until today.
See also the following functions:
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.
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.
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:
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.
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 .
TIME("0;0;0") returns 00:00:00
TIME("4;20;4") returns 04:20:04
See also the following functions:
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.
TIMEVALUE("Text")
Text is a valid time expression and must be entered in quotation marks.