Editing data
Adding a column and filtering
Component
Adding a calculation field
The document provides some commonly used date combination formulas for you to use directly.
Function List
Returns the date of the specified level.
MONTH
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.
YEAR
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.
QUARTER
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.
WEEK
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.
DAY
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.
WEEKDAY
Returns the day of the week corresponding to a date.
WEEKDAY("2005/9/10") returns 6 (Saturday).
WEEKDAY("2005/9/11") returns 0 (Sunday).
HOUR
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.
MINUTE
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.
SECOND
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.
WEEKDATE
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
YEARDELTA
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.
DATEDIF
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.
DATEDELTA
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.
DAYS360
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.
DATESUBDATE
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
NOW
Returns the current time.
If the system time is 15:18:38 on May 12, 2012, NOW() returns 2012-05-12 15:18:38.
TODAY
Returns the current date.
If the system date is September 10, 2005, TODAY() returns 2005-09-10.
DAYSOFQUARTER
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.
DAYSOFMONTH
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.
DATETONUMBER
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.
DAYSOFYEAR
Returns the number of days in a certain year.
DAYSOFYEAR(2008) returns 366.
DAYSOFYEAR("2008-01-01") returns 366.
DAYVALUE
Returns the number of days from 1900 to the specified date.
DAYVALUE("2008-08-08") returns 39667.
Date type shift
DATE
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.
LUNAR
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.
TODATE
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.
TIME
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).
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.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy