excel time function

excel time function

In our daily table processing, date and time are the most common types data. The following is a summary of how to use the time function in excel.

Recommended tutorial: excel tutorial

1. Basic date format setting

Excel supports multiple date representation methods , common date formats are: December 18, 2018, December 28, 2018, 2018-12-28, 18-Dec-2018, etc., and the time format is: 19:23:22 , 7:23:22PM, etc.

If you need a different date display format, you can set it in [Date] or [Customize] in the number in the cell format setting.

Special note: The 2018.12.18, 20181218 and other representation formats we usually use cannot be recognized as dates in Excel. Please avoid using this format.

2. Basic date functions

1. The year

year function is used to extract the year from the date.

Syntax: =year(Serial_number)

Result: Return the year information between 1900-9999.

2.month

The month function is used to extract the month from the date.

Syntax: =month(Serial_number)

Result: Returns a number between 1 (January) and 12 (December).

3.day

The day function is used to extract the day from the date.

Syntax: =day(Serial_number)

Result: Returns the value of the day of the month, between 1 and 31.

4.date

The date function returns the serial number representing a specific date based on the year, month and day.

Syntax: =DATE(year,month,day)

Result: Returns the number representing the date in Excel date time code.

Parameter description:

①If year is between 0 (zero) and 1899 (inclusive), Excel will add 1900 to the value and then calculate the year. (For the 1900 date system).

②month represents the number of the month in each year. If the entered month is greater than 12, it will be calculated from January of the specified year upwards. For example: DATE(2018,14,2) returns the serial number representing February 2, 2019. If the entered month is less than 0, the corresponding number of months will be subtracted from December of the year before the specified year. For example: DATE(2018,-3,2) returns the serial number representing September 2, 2017.

③day represents the number of the day in the month. If day is greater than the maximum number of days in the month, it will be accumulated from the first day of the specified month upwards.

5.today

day extracts the system date

Syntax:=today()

Result: Returns the current date format date. The result of the calculation is variable.

6.now

now function extracts the system date and time.

Syntax:=now()

Result: Returns the current date and time in date format. The result of the calculation is variable.

Tips: Enter the current system date: ctrl; Enter the current system time: ctrl shift;

3. Basic time functions

1.hour

hour extracts the number of hours in the time.

Syntax: =hour(Serial_number)

Result: Returns the hour value, which is between 0 (12:00 A.M) and 23 (11:00 P.M) integer.

2.minute

minuteThe number of minutes in the extraction time.

Syntax: =minute(Serial_number)

Result: Returns the minute value, which is an integer between 0 and 59.

3.second

The second function extracts the number of seconds in the time.

Syntax: =second(Serial_number)

Result: Returns the second value, which is an integer between 0 and 59.

4.time

The time function returns a sequence of specific times based on hours, minutes and seconds.

Syntax: =time(hour,minute,second)

Result: Returns the number of series at a specific time.

Parameter description:

①hour is a value between 0 (zero) and 32767, representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as hours. For example: time(27,0,0)=time(3,0,0)=.125 or 3:00 AM.

②minute is a value between 0 and 32767, representing minutes. Any value greater than 59 will be converted to hours and minutes. For example: TIME(0,750,0)=TIME(12,30,0)=.520833 or 12:30 PM.

③second is a value between 0 and 32767, representing seconds. Any value greater than 59 will be converted to hours, minutes and seconds. For example: TIME(0,0,2000)=TIME(0,33,22)=.023148 ​​or 12:33:20 AM.

The above is the detailed content of excel time function. For more information, please follow other related articles on the PHP Chinese website!