WPS Table Date and Time function

(i) Date and time functions
1.DATE
Purpose: Returns the serial number that represents a specific date.
Syntax: Date (year, month, day)
Parameters: Year, for one to four bits, the parameter is interpreted according to the date system used. By default, the WPS table uses the 1900 date system. month, representing the number of months of the year. If the month you enter is greater than 12, the addition operation is performed from January of the specified year. Day represents the number of days in the month. If day is greater than the maximum number of days of the month, it is incremented from the first of the specified month.
Note: The WPS table saves the date in sequential sequence number so that it can be evaluated. If the workbook uses a 1900 date system, the WPS table saves December 31, 1899 as a serial number 1. Similarly, January 1, 1998 will be saved as a serial number 35796 because the date is 35,796 days from January 1, 1900.
Example: If you use the 1900 date system (the WPS table defaults), the formula "=date (2001,1,1)" returns 36892.
2.DATEVALUE
Purpose: Returns the serial number of the date represented by the date string. The main purpose of this function is to convert the date that the text represents to a serial number.
Syntax: DATEVALUE (date string)
Parameters: A date string that is the text that represents the date in the WPS table date format. In using the 1900 date system, the date string must be a date from December 31, 1899 to December 31, 9999. If the date string exceeds the range above, the function DateValue returns the error value #value!. If the age in the parameter date string is omitted, the function datevalue the current age of the internal clock of the computer system, and the time information in the date string is ignored.
Example: Formula "=datevalue" ("2001/3/5") "returns 36955,
=datevalue ("2-26") returns 39504.
3.DAY
Purpose: Returns the number of days of a date that is represented by a serial number (integer 1 through 31).
represented by integers 1 through 31.
Syntax: Day (date ordinal)
Parameter: The date ordinal is the number of days to look up, and it has several types of input: quoted text strings (such as "1998/01/30"), serial numbers (such as January 30, 1998 in the 1900 date system 35825), and the results of other formulas or functions, such as DateValue (" 1998/1/30 ")).
Instance: Formula "=day (" 2001/1/27 ")" returns 27,=day (35825) returns 30,=day (DATEVALUE ("2008/4/5")) returns 5.
4.EDATE
Purpose: Returns the date serial number of the specified month before or after the specified date.
Syntax: EDATE (start date, number of months)
Parameter: The start date parameter represents the start date, and it has several input methods: the quoted text string (for example: "1998/01/30"), the serial number (such as 35825 for January 30, 1998), or the results of other formulas or functions (for example: DATEVALUE ("1998/1/30") )。 Month is the number of months before or after the start_date, the future date is indicated by positive numbers, and the past dates are represented by negative numbers.
Example: Formula "=edate" ("2008/1/5", 2) "returns 39512, i.e. March 5, 2008, =edate (" 2008/8/5 ",-5) returns 39512 or March 5, 2008.
6.EOMONTH
Purpose: Returns the serial number of the last day of the specified month before or after the start date.
Syntax: EOMONTH (start date, number of months)
Parameter: The start date parameter represents the start date, which has several input methods: the quoted text string (such as "1998/01/30"), the serial number (for example, 35825 in the 1900 date system), or the results of other formulas or functions (such as DateValue ("1998/1/30")). Month is the number of months before or after start_date, positive for future date, negative for past date.
Example: Formula "=eomonth" ("2001/01/01", 2) "returns 36981 i.e. March 31, 2001, =eomonth (" 2001/01/01 ",-6) returns 36738 or July 31, 2000.
7.HOUR
Purpose: Returns the number of hours for the time value. That is between 0 (12:00 a.m.) An integer to the 11:00 p.m.
Syntax: HOUR (date ordinal)
Parameter: The date ordinal represents a time value that contains the number of hours to return. It has several input methods: a quoted text string (such as "6:45pm"), a decimal number (such as 0.78125 for 6:45pm), or the result of another formula or function (such as TimeValue ("6:45 PM").
Instance: Formula "=hour (" 3:30:30 PM ")" returns 15,=hour (0.5) returns 12 that 12:00:00 Am,=hour (29747.7) returns 16.
8.MINUTE
Purpose: Returns the Minutes in the time value, which is an integer between 0 and 59.
Syntax: MINUTE (date ordinal)
Parameter: The date ordinal is a time value that contains the number of minutes to find. Time can be entered in several ways: quoted text strings (such as "6:45pm"), decimal digits (such as 0.78125 for 6:45 pm) or other formulas or functions (such as TimeValue ("6:45 PM").
Instance: the formula "=minute (" 15:30:00 ")" returns 30,=minute (0.06) back to 26,=minute (TimeValue ("9:45 PM")) to return 45.
9.MONTH
Purpose: Returns the month in the date indicated by the serial number, which is an integer between 1 (January) and 12 (December).
Syntax: MONTH (date ordinal)
Parameter: The date ordinal represents a date value that contains the month to look up. Dates can be entered in a variety of ways: quoted text strings (such as "1998/01/30"), serial numbers (such as 35825 that represent January 30, 1998) or the results of other formulas or functions (such as DateValue ("1998/1/30")).
Instance: Formula "=month (" 2001/02/24 ")" returns 2,=month (35825) returns 1,=month (DATEVALUE ("2000/6/30")) returns 6.
10.NETWORKDAYS
Purpose: Returns the complete working day (excluding weekends and specially designated holidays) between the parameter start and end dates.
Syntax: networkdays (start date, end date, vacation)
The list can be a range of cells that contains dates, or an array constant consisting of serial numbers that represent dates. The dates in a function can be entered in several ways: the quoted text string (such as "1998/01/30"), the serial number (for example, using the 1900 date system 35825), or the results of other formulas or functions (such as DATEVALUE ("1998/1/30")).
Note: This function can only be used after loading the Analysis ToolPak.
11.NOW
Purpose: Returns the serial number corresponding to the current date and time.
Syntax: Now ()
Parameters: None
Instance: If you are using a 1900 date system and your computer's internal clock is 2008-4-5 22:34, the formula "=now ()" returns 39543.940625.
12.SECOND
Purpose: Returns the number of seconds for the time value (an integer from 0 to 59).
Syntax: SECOND (date ordinal)
Parameter: The date ordinal represents a time value that contains the number of seconds to find. The way to enter the time is shown in the above section.
Instance: Formula "=second (" 3:30:26 PM ")" returns 26,=second (0.016) returns 2.
13.TIME
Purpose: Returns a small value at a specified time, which returns a small number from 0 to 0.99999999, representing the time between 0:00:00 (12:00:00 a.m) and 23:59:59 (11:59:59 p.m).
Syntax: Time (hours, minutes, seconds)
Parameters: The number of hours is 0 to 23, the number between 0 to 59, and the number of seconds between 0 and 59.
Instance: the formula "=time (12,10,30)" Returns the serial number 0.51, equivalent to 12:10:30 PM. =time (9,30,10) returns the serial number 0.40, equivalent to 9:30:10 AM. =text (Time (23,18,14), "H:mm:ss am/pm") returns "11:18:14 PM".
14.TIMEVALUE
Purpose: Returns the time decimal value represented by a text string. The decimal value is a value from 0 to 0.999999999, representing the time between 0:00:00 (12:00:00 AM) and 23:59:59 (11:59:59 PM).
Syntax: TimeValue (Time string)
Parameter: The time string is a text string (such as "6:45 PM" and "18:45") that represents time in a WPS table time format.
Instance: the formula "=timevalue" ("3:30 AM") returns 0.145833333,=timevalue ("2001/1/26 6:35 AM") to return 0.274305556.
15.TODAY
Purpose: Returns the serial number of the system's current date.
Parameters: None
Syntax: Today ()
Instance: the formula "=today ()" returns 2008-4-5 (the system time when the formula was executed).
16.WEEKDAY
Purpose: Returns the number of weeks of a date. By default, its value is an integer between 1 (Sunday) and 7 (Saturday).
Syntax: Weekday (date ordinal, return value type)
Parameter: The date ordinal is the date on which you want to return the number of dates, and it has several types of input: quoted text strings (such as "2001/02/26"), serial numbers (such as 35825 for January 30, 1998), or other formulas or function results (such as DateValue ("2000/1/30")). The return value type is a number that determines the type of return value, and the number 1 or omitted 1 to 7 represents Sunday to Saturday, the number 2 1 to 7 represents Monday to Sunday, and the number 3 to 0 represents Monday to Sunday.
Example: Formula "=weekday" ("2008/4/5", 2) "returns 6 (Saturday), =weekday (" 2008/4/5 ", 3) returns 5 (Saturday).
17.WEEKNUM
Purpose: Returns a number that represents the week ordinal of a year.
Syntax: WEEKNUM (date ordinal, return value type)
Parameter: The date ordinal represents the date of the week. You should enter a date using the Date function, or enter the date as the result of another formula or function. The return value type is a number that determines which day the week calculation begins. The default value is 1.
18.WORKDAY
Purpose: Returns the value of a date for a specified weekday (excluding weekends and specially designated holidays) before or after a date (start date), excluding weekends or holidays.
Syntax: WORKDAY (start date, days, holidays)
Parameters: Days are days before or after the start date excluding weekends and holidays; A positive value produces a future date, a negative value produces a past date; a vacation is an optional list of dates that you want to exclude from the work calendar, such as statutory or illegal holidays. The manifest can be a range of cells that contains dates, or an array constant consisting of serial numbers that represent dates. Dates can be entered in a variety of ways: quoted text strings (such as "1998/01/30"), serial numbers (such as the 1900 date system when 35825 represents January 30, 1998), or other formulas or functions (e.g. DateValue ("1998/1/30")).
19.YEAR
Purpose: Returns the year of a date. The result is an integer between 1900 and 9999. (3lian material)
Syntax: Year (date ordinal)
Parameter: The date ordinal is a date value that contains the year to find. Dates can be entered in a variety of ways: quoted text strings (for example, "1998/01/30"), serial numbers (for example, if you use the 1900 date system then 35825 for January 30, 1998) or the results of other formulas or functions (for example, DateValue ("1998/1/30")).
Instance: Formula "=year (" 2008/4/5 ") returns 2008", =year ("2003/05/01") returns 2003,=year (35825) returns 1998.
20.YEARFRAC
Purpose: Returns the number of days between the start and end dates as a percentage of the total number of days in the year.
Syntax: Yearfrac (start date, end date, base option)
Parameters: The dates in a function can be entered in a variety of ways: quoted text strings (such as "1998/01/30"), serial numbers (such as 35829 for January 30, 1998 in the 1900 date system), or other formulas or functions (e.g. DateValue ("1998/1/30") )。 The benchmark option represents the day Count datum type, where 0 or omitted for us (NASD) 30/360,1 actual days/actual days, 2 actual days/360,3 actual days/365,4 Europe 30/360.
Example: the formula "=yearfrac" ("2001/01/31", "2001/06/30", 0) "returned to 0.416666667,yearfrac (" 2001/01/25 "," 2001/09/27 ") returned 0.67222.