Usage Scope
| Position | |
|---|---|
Editing data | |
Component |
Note:The document provides some commonly used date combination formulas for you to use directly.
Function List
| Classification | Function | Usage | Example |
|---|---|---|---|
Returns the date of the specified level. | Returns the month of a date. | MONTH() returns 10, which is the month of 2020-10-23 15:36:25 (system server time). MONTH("2000/1/1") returns 1. MONTH("1997-04-20","yyyy-MM-dd") returns 4. | |
Returns the year of a date. | YEAR() returns 2020, which is the year of 2020-10-23 15:36:25 (system server time). YEAR("2000/1/1") returns 2000. YEAR("1997-04-20","yyyy-MM-dd") returns 1997. | ||
Returns the quarter of a date. | QUARTER() returns 4, which is the quarter of 2020-10-23 15:36:25 (system server time). QUARTER("2000/1/1") returns 1. QUARTER("1997-04-20","yyyy-MM-dd") returns 2. | ||
Returns the number of the week. | Returns a number representing the week of the year. WEEK() returns 52, which is the week number of 2010-1-1 15:36:25 (system server time). WEEK("2010/1/1") returns 52. WEEK("2019/1/5","F1") returns 1. WEEK("2010/1/6") returns 1. | ||
Returnss the day of the date. | DAY() returns 23, which is the day of 2020-10-23 15:36:25 (the system server time). DAY("2000/1/1") returns 1. DAY("1997-04-20","yyyy-MM-dd") returns 20. | ||
Returns the day of the week corresponding to a date. | WEEKDAY("2005/9/10") returns 6 (Saturday). WEEKDAY("2005/9/11") returns 0 (Sunday). | ||
Returns the hour of a time value. | HOUR() returns 15, which is the hour of 2020-10-23 15:36:2 (system server time). HOUR("11:32:40") returns 11. HOUR("11:32:40","HH:mm:ss") returns 11. | ||
Returns the minute of a specified time. | MINUTE() returns 36, which is the minute of 2020-10-23 15:36:25 (system server time). MINUTE("15:36:25") returns 36. MINUTE("15:36:25","HH:mm:ss") returns 36. | ||
Returns the second of a specified time. | SECOND() returns 25, which is the second of 2010-1-1 15:36:25 (system server time). SECOND("15:36:25") returns 25. SECOND("15:36:25","HH:mm:ss") returns 25. | ||
Returns the date corresponding to a specified day of a specified week in a specified month of a specified year. | WEEKDATE(2009,10,2,1) returns 2009-10-04, the first day (Sunday) of the second week in October 2009. WEEKDATE(2009,12,1,-1) returns 2009-12-05, the last day (Saturday) of the first week in December 2009. | ||
Date Calculation | Returns the date several years (specified by delta) after the specified date. | YEARDELTA("2008-10-10",10) returns 2018-10-10. | |
MONTHDELTA | Returns the date delta months after the specified date. | MONTHDELTA("2008-08-08",4) returns 2008-12-08. | |
Returns the number of days, months, or years between two specified dates. | DATEDIF("2001/2/28","2004/3/20","Y") returns 3, indicating that 3 full years exist between February 28, 2001 and March 20, 2004. DATEDIF("2001/2/28","2004/3/20","M") returns 37, indicating that 37 full months exist between February 28, 2001 and March 20, 2004. | ||
Returns the date a specified time period after a specified date. | DATEDELTA("2008-08-08",-10) returns 2008-07-29. DATEDELTA("2008-08-08",10) returns 2008-08-18. | ||
Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. | DAYS360("1998/1/30","1998/2/1") returns 1. | ||
Returns the time interval between two dates. | DATESUBDATE("2008-08-08","2008-06-06","h") returns 1512. DATESUBDATE("2008-06-06","2008-08-08","H") returns –1512. | ||
Date Extraction | Returns the current time. | If the system time is 15:18:38 on May 12, 2012, NOW() returns 2012-05-12 15:18:38. | |
Returns the current date. | If the system date is September 10, 2005, TODAY() returns 2005-09-10. | ||
Returns the number of days in a certain quarter of a certain year since January 1900. | DAYSOFQUARTER("2009-02-01") returns 90. DAYSOFQUARTER("2009/05/05") returns 91. | ||
Returns the number of days in a certain month of a certain year since January 1900. | DAYSOFMONTH("1900-02-01") returns 28. DAYSOFMONTH("2008/04/04") returns 30. | ||
Returns the number of milliseconds from January 1, 1970 00:00:00 GMT to the specified date. | DATETONUMBER("2008-08-08") returns 1,218,124,800,000. | ||
Returns the number of days in a certain year. | DAYSOFYEAR(2008) returns 366. DAYSOFYEAR("2008-01-01") returns 366. | ||
Returns the number of days from 1900 to the specified date. | DAYVALUE("2008-08-08") returns 39667. | ||
Date type shift | Returns the sequential serial number that represents a particular date. | DATE(1978,9,19) returns 1978-09-19. DATE(2000,13,05) returns 2001-01-05. DATE(2000,3,35) returns 2000-04-04. | |
Returns the current date corresponds to the lunar time. | If you need to check July 21, 2011 corresponding to the lunar time, just enter the LUNAR (2011,7,21). The results will be displayed as: 辛卯年六月廿一. The same, input LUNAR (2001,7,21), the output is 辛巳年六月初一. The formula supports 1900-2100 period. | ||
Returns parameters of all date types to dates. | TODATE(DATE(2007,12,12)) returns date object of 2007-12-12. TODATE(1023542354746) returns date object of 2002-06-08. | ||
Returns the specified time of a date. | TIME(14,40,0) returns 14:40:00 of the system time (year-month-day). TIME(19,43,24) returns 19:43:24 of the system time (year-month-day). |
Common Date Formula
When dealing with date-type data, you often need to perform operations such as getting the current date, the current time, and the year, month, and day of a date.
Previous：Special Character Processing
