## 日期公式

关键词:  日期公式

Formulas Description Examples
DATE Syntax: DATE(year, month, day)
Converts a date written as year, month, day to an internal serial number and displays it in the cell's formatting. Year is an integer between 1583 and 9956 or 0 and 99. Month is an integer between 1 and 12. Day is an integer between 1 and 31.
=DATE(2012, 06, 26)
DATEVALUE Syntax: DATEVALUE(date_text)
Converts a date that is stored as text to a serial number that it recognizes as a date.
=DATEVALUE("2012-06-26")
DAY Syntax: DAY(date_value)
This function returns the day of the month (a number from 1 to 31) given a date value.
=DAY(today())
DAYS Syntax: DAYS(end_date, start_date)
This function returns the number of days between two dates.
=DAYS(A1,A2)
=DAYS(2013-02-01,2013-12-22)
DAYS360 Syntax: DAYS360(start_date, end_date, [method])
This function returns the number of days between two dates based on a 360-day year.
start_date and end_date are the two dates to calculate the difference between.
method is optional. It is a boolean value - either TRUE or FALSE. If TRUE is entered, the DAYS360 function will use the US method. If FALSE is entered, the DAYS360 function will use the European method.
=DAYS360(A1,A2)
=DAYS360(2013-02-01,2013-12-22)
EDATE Syntax: EDATE(Start_date, months)
Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date).
=EDATE(today(), 2)
EOMONTH Syntax: EOMONTH(Start_date, months)
Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
=EOMONTH(today(), 2)
HOUR Syntax: Hour(serial_number)
This function returns the hour of a time value (from 0 to 23).
=DAY(A1)
=DAY(12:52:35)
MINUTE Syntax: MINUTE(serial_number)
This function returns the minute of a time value (from 0 to 59).
=MINUTE("8:28:10")
MONTH Syntax: MONTH(date_value)
This function returns the month (a number from 1 to 12) given a date value.
=MONTH(2013-12-22)
NETWORKDAYS Syntax: NETWORKDAYS(start_date,end_date, [holidays])
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.
=NETWORKDAYS(A1, A2, B1:B5)
NETWORKDAYS.INTL Syntax: NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.
=NETWORKDAYS.INTL(A1, A2, 1, B1:B5)
NOW Syntax: NOW()
Returns the current computer system date time. NOW is a function without arguments.
=NOW()
SECOND Syntax: SECOND(serial_number)
This function returns the second of a time value (from 0 to 59).
=SECOND("8:28:18")
=SECOND("3:08:18 PM")
=SECOND("8:28:18")
TIME Syntax: TIME(hour, minute, second)
This function returns the decimal number for a particular time.
=TIME(0,650,0)
=TIME(A1,A2,A3)
TIMEVALUE Syntax: TIMEVALUE(date_text)
Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
=TIMEVALUE("2012-06-26 20:30")
TODAY Syntax: TODAY()
Returns the current computer system date. TODAY is a function without arguments.
=TODAY()
WEEKDAY Syntax: WEEKDAY(serial_number, [type])
Returns the day of the week for the given serial_number (date value). The day is returned as an integer based on the type.
Type is option. It can be any of the following values:
type = 1 (default), the weekdays are counted starting from Sunday (Monday = 2).
type = 2, the weekdays are counted starting from Monday (Monday = 1).
type = 3, the weekdays are counted starting from Monday (Monday = 0).
=WEEKDAY(A1)
=WEEKDAY("2010/10/13")
WEEKNUM Syntax: WEEKNUM(serial_number, [type])
Returns a number that indicates where the week falls numerically within a year. The WEEKNUM function considers the week containing January 1 to be the first week of the year.
Type is option. It can be any of the following values:
type = 1 (default), Week begins on Sunday.
type = 2, Week begins on Monday.
=WEEKNUM(A1)
=WEEKNUM(Date(2010,10,13), 2)
WORKDAY Syntax: WORKDAY(start_date, days, [holidays])
Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.
=WORKDAY(A1, 10, B1:B5)
WORKDAY.INTL Syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays])
Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.
=WORKDAY.INTL(A1, 10, 1, B1:B5)
YEAR Syntax: YEAR(date_value)
This function returns a four-digit year (a number from 1900 to 9999) given a date value.
=YEAR(A1)
=YEAR("2013/10/13")
YEARFRAC Syntax: YEARFRAC(start_date, end_date, basis)
Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.
=YEARFRAC(A2,A3,A4)