Excel function explanation Date and time function

1. Excel date and time function: Date

Use returns the serial number that represents a specific date.

"Syntax" DATE (year,month,day)

The "argument" year is one to four bits, which is interpreted according to the date used by the system. 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, Excel date and time function: DATEVaLUE

"Use" 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)

The 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 between January 1, 1900 and December 31, 9999, while in the 1904 date system, Date_text must be January 1, 1904 to 9999 12 A date between 31st of the month. If the date_text exceeds the range above, the function DATEVaLUE returns an error value of #value!. 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.

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

3. Excel date and Time functions: Day

Use returns the number of days of a date that is represented by a serial number (integer 1 through 31), expressed as an integer 1 through 31.

"Syntax" Day (Serial_number)

The "parameter" Serial_number 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 DAT Evalue ("1998/1/30")).

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

4, Excel date and time function: DAYS360

"Use" returns the number of days between two days, based on an algorithm of 360 days a year (30 days per month, 12 months for each year).

"Syntax" DAYS360 (Start_date,end_date,method)

The parameters start_date and end_date are the starting and ending dates for calculating the period days. If start_date after end_date, DAYS360 returns a negative number. 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, 35825 means January 30, 1998), or the results of other formulas or functions (for example, date VaLUE ("1998/1/30")). method is a logical value that specifies whether the European or American approach 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 expiration date is equal to 1st for the next one months, otherwise the expiration date equals this month's 30th if true. The European method (either the starting date or the 31st number with the end date of one months will be equal to 30th this month).

"Instance" formula "=days360" ("1998/2/1", "2001/2-1") "returns 1080."

5, Excel date and time function: EDATE

Use returns the date serial number of the specified month before or after the specified date (start_date).

"Syntax" EDATE (start_date,months)

The parameters 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 a 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.

The 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, Excel date and time function: EOMONTH

Use returns the serial number of the last day of the specified month before or after Start-date.

"Syntax" EOMONTH (start_date,months)

The parameter start_date parameter represents the start date, and it has several input methods: the quoted text string (such as "1998/01/30"), the serial number (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.

The 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, Excel date and time function: HOUR

"Purpose" returns the number of hours for a time value. That is between 0 (12:00 a.m.) To 11:00 (p.m.) An integer between.

"Syntax" HOUR (Serial_number)

The parameter serial_number represents a time value that contains the number of hours to return. It has multiple input methods: quoted text strings (such as "6:45 pm"), decimal numbers (such as 0, 78125 for 6:45pm), or other formulas or functions (such as timevalue ("6:45 pm").

The instance Formula =hour ("3:30:30 PM") returns 15,=hour (0.5) returns 12 that 12:00:00am,=hour (29747.7) returns 16.

8, Excel date and time function: MINUTE

Use returns the Minutes in the time value, which is an integer between 0 and 59.

"Syntax" MINUTE (Serial_number)

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

The instance Formula =minute ("15:30:00") returns 30,=minute (0, 06) back to 26,=minute (TimeValue ("9:45 PM")) to return 45.

9, Excel date and time function: MONTH

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

The 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")).

The instance Formula =month ("2001/02/24") returns 2,=month (35825) back to 1,=month (DATEVaLUE ("2000/6/30")) to return 6.