Excel Date Time function

This article describes the Excel date time function

1.DATE


Purpose: Returns the serial number that represents a specific date.


syntax: DATE (year,month,day)


parameter: Year is one to four bits, the parameter is interpreted according to the date system used. By default, Excel for Windows uses the 1900 date system, and the Excel for Macintosh uses the 1904 date system. Month represents 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: Excel saves dates in sequential sequence numbers so that they can be evaluated. If the workbook uses a 1900 date system, Excel saves January 1, 1900 as a serial number 1. Similarly, January 1, 1998 will be saved as a serial number 35796 because the date is 35,795 days from January 1, 1900.


instance: If the 1900 date system (Excel default) is used, the formula "=date (2001,1,1)" returns 36892.


2.DATEVaLUE


Purpose: Returns the serial number of the date represented by Date_text. The main purpose of this function is to convert the date that the text represents to a serial number.


syntax: DATEVaLUE (date_text)


parameter: Date_text is the text that represents the date in Excel date format. In using the 1900 date system, the Date_text must be a date from January 1, 1900 to December 31, 9999, and in the 1904 date system, the Date_text must be a date between January 1, 1904 and December 31, 9999. If the date_text exceeds the range above, the function DateValue returns an error value of #value!. (3lian.com)


if the age in the parameter date_text is omitted, the function DateValue uses the current age of the internal clock of the computer system, and the time information in the Date_text is ignored.


Instance: Formula "=datevalue (" 2001/3/5 ")" returns 36955,datevalue ("2-26") to return 36948.


3.DAY


Purpose: Returns the number of days of a date that is represented by a serial number (integer 1 through 31), expressed as integers 1 through 31.


syntax: Day (serial_number)


parameter: Serial_number is the number of days to find, it has several types of input: quoted text strings (such as "1998/01/30"), serial numbers (such as the 1900 date system 35825, January 30, 1998), and other formulas or functions results ( such as DateValue ("1998/1/30")).


instance: Formula "=day (" 2001/1/27 ")" returns 27,=day (35825) returns 30,=day (DATEVaLUE ("2001/1/25")) returns 25.


4.days360


use: According to the 360-day algorithm (30 days a month, 12 months a year), returns the number of days between two days.


syntax: DAYS360 (Start_date,end_date,method)


parameters: Start_date and end_date are the starting and ending dates for calculating the period days. If Start_date is end_date, then DAYS360 returns a negative number. Dates can be entered in several ways: quoted text strings (for example: "1998/01/30"), serial numbers (for example, if you use a 1900-date system, 35825 means January 30, 1998) or the results of other formulas or functions (for example, DATEVaLUE ("1998/1/ 30 ")).


method is a logical value that specifies whether a European or American method is used in the calculation. If False or omitted, the United States method is used (if the starting date is 31st of one months, it is equal to 30th of the same month). If the expiration date is 31st of one months and the starting date is earlier than 30th, the expiry date is equal to 1st for the next one months, otherwise the expiry date is the same as this month's 30th. If true, the European method (either the start date or the ending date of one months of 31st will be equal to 30th this month).


instance: Formula "=days360" ("1998/2/1", "2001/2-1") "returns 1080."


5.EDATE


Purpose: Returns the date serial number of the specified month before or after the specified date (start_date).


syntax: EDATE (start_date,months)


parameter: The start_date parameter represents the start date, and it has several types of input: a quoted text string (for example: "1998/01/30"), a serial number (such as 35825 for January 30, 1998), or the result of another formula or function (for example: DATEVaLUE (" 1998/1/30 ")). Months is the number of months before or after start_date, the future date is indicated by positive numbers, and the past dates are represented by negative numbers.


instance: Formula "=edate (" 2001/3/5 ", 2)" returns 37016 i.e. May 5, 2001, =edate ("2001/3/5",-6) returns 36774 or September 5, 2000.


6.EOMONTH


Purpose: Returns the serial number of the last day of the specified month before or after Start-date. (www.3lian.com)


syntax: EOMONTH (start_date,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 dates, and negative for past dates.


instance: 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 a time value. That is between 0 (12:00 a.m.) An integer to the 11:00 p.m.


syntax: HOUR (serial_number)


parameter: Serial_number represents a time value that contains the number of hours to return. It has several input methods: a quoted text string (such as "6:45 PM"), 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 is 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 (serial_number)


parameter: Serial_number 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:45 pm"), decimal digits (such as 0.78125 for 6:45 pm) or other formulas or functions (such as timevalue ("6:45 pm").


instance: Formula "=minute (" 15:30:00 ")" returns 30,=minute (0.06) returns 26,=minute (TimeValue ("9:45 PM")) returns 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 (serial_number)


parameter: Serial_number 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 Start-data and End-data.


syntax: networkdays (start_date,end_date,holidays)


parameters: Start_date on behalf of the start date, end_date on behalf of the day of termination; Holidays is an optional area, statutory holidays, and other non-statutory holidays that represent one or more dates that are not in the working calendar. The list can be a range of cells that contains dates, or an array constant consisting of serial numbers that represent dates.

The date in the
function can be entered in several ways: a quoted text string (such as "1998/01/30"), a serial number (for example, 35825 with a 1900 date system), or a result of another formula or function (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 ()


parameter: No


instance: If you are using a 1900 date system and your computer's internal clock is 2001-1-28 12:53, the formula "=now ()" returns 36919.54.