Date Functions
Date function is a type of function in a formula that can operate on date-type data.
TODAY()
Returns today's date (year month day), but not the exact minute (default is 00:00:00).If you want exact timing, use the NOW() function.
TODAY()
Parameter Description
This function requires no parameters.
You can use this function directly to return the year, month and day, as shown in example 1;
Can also be used with functions such as 'DATEADD() ' or ' DATETIME_DIFF() ', such as subtracting the current time from {due} to display the countdown of an item, as shown in Example 2.
Note ① : The result returned by this function is updated only when the formula is refreshed or the table is refreshed.
Example
// Return the year month day.
Formula: TODAY ()
Result: 2021/03/25 00:00
// Displays the countdown of the item.The following field {deadline} is of date type and the cell value is 2021/03/31.
Formula: DATETIME_DIFF({deadline}, TODAY(), "days")
Result: 6
NOW()
Return today's date and time, will be accurate when the minute.
NOW()
Parameter Description
This function requires no parameters.
You can use this function directly to return the year, month and day, as shown in example 1; can also be used with functions such as
DATEADD()orDATETIME_DIFF(), such as subtracting the current time from {due} to display the countdown of an item, as shown in Example 2.Note ① : The result returned by this function is updated only when the formula is refreshed or the table is refreshed.
Example
// Return the year month day.Set the display time to 24 hours.
Formula: NOW ()
Result: 2021/03/25 18:59
// Displays the countdown of the item.The following field {deadline} is of date type and the cell value is 2021/03/31.
Formula: DATETIME_DIFF({end time}, NOW(), "days")
Result: 5
TONOW()
Returns the difference between the current date and the specified date (no positive or negative).
TONOW(date, units)
Parameter Description
date: It is the specified date, that is, subtract the current date from the specified date, then calculate the number of days (custom time unit) between the two dates. units: A unit of time measuring the difference between a specified date and the current date. For example, the "day" calculation can also be converted to the "year" calculation.
The unit of timing includes the following symbols, which can be used in either format: "Unit specifier" → "Abbreviation" milliseconds: milliseconds → ms seconds: seconds → s minutes: "minutes" → "m" hours: hours → h days: "days" → "d" weeks: "weeks" → "w" Month: "months" → "M" quarters: "quarters" → "Q" Year: "years" → "y"
click on the links to view all timing unit.
Example
// Returns the difference between the current date and 2021/03/20, assuming the current date is 2021/03/25.
Formula: TONOW("2021/03/20", "d")
Result: 5
// Display the project time.The following field {start date} is of date type and the cell value is 2021/01/01, assuming the current date is 2021/03/25.
Formula: TONOW({start date}, "months")
Result: 2
FROMNOW()
Returns the difference between the current date and the specified date (no positive or negative).
FROMNOW(date, units)
Parameter Description
date: indicates the specified date. By subtracting the current date from the specified date, the difference between two dates is calculated in days (custom timing unit). units: units of time, that is, units in which the difference between the specified date and the current date is calculated. For example, "days" can also be converted to "years".
The unit of timing includes the following symbols, which can be used in either format: "Unit specifier" → "Abbreviation" milliseconds: milliseconds → ms seconds: seconds → s minutes: "minutes" → "m" hours: hours → h days: "days" → "d" weeks: "weeks" → "w" Month: "months" → "M" quarters: "quarters" → "Q" Year: "years" → "y"
Click on the links to view all timing unit.
Example
// Returns the difference between the current date and 2021/03/20, assuming the current date is 2021/03/25.
Formula: FROMNOW("2021/03/20", "d")
Result: 5
// Displays the countdown of the item.The following field {start date} is of date type and the cell value is 2021/01/01, assuming the current date is 2021/03/25.
Formula: FROMNOW({start date}, "months")
Result: 2
DATEADD()
Adds a fixed interval to the specified date.
DATEADD(date, count, units)
Parameter Description
date: is the date you specify.This function increments the date by a certain interval. count: This is the time interval, which supports the input of positive and negative numbers.If the value is positive, it means an increase of several days (the timing unit can be customized). See Example 1. If it is negative, it means a reduction of several days. See Example 2. units: indicates the unit of timing, that is, the unit by which time intervals are increased.It is also possible to convert calculations based on "days" into calculations based on "years".
Units of timing include the following symbols, which can be used in either format: "unit specifier" → "Abbreviation" milliseconds: milliseconds → ms seconds: seconds → s minutes: "minutes" → "m" hours: hours → h days: "days" → "d" weeks: "weeks" → "w" Month: "months" → "M" quarters: "quarters" → "Q" Year: "years" → "y"
Click on the links to view all timing unit.
Example
// Add one day to 2021/03/25.The unit of time "days" means "days".
Formula: DATEADD("2021/03/25", 1, "days")
Result: 2021/03/26
// Reduce the time interval by 1 day to 2021/03/25.Timing Unit "days" means counting in "days".
Formula: DATEADD("2021/03/25", -1, "days")
Result: 2021/03/24
// Add a 10-day interval to {startup time}.The following field {Start time} is of date type and the cell value is 2021/03/25.Timing Unit "days" means counting in "days".
Formula: DATEADD({start time}, 10, "days")
Result: 2021/04/04
DATETIME_DIFF()
Returns the difference (plus or minus) between two dates, date 1 minus date 2.
DATETIME_DIFF(date1, date2, units)
Parameter Description
date1: date1. date2: date2.
Units: unit of time, the unit in which the difference between date 1 and date 2 is calculated.For example, the "day" calculation can also be converted to the "year" calculation.
Units of timing include the following symbols, which can be used in either format: "unit specifier" → "Abbreviation" milliseconds: milliseconds → ms seconds: seconds → s minutes: "minutes" → "m" hours: hours → h days: "days" → "d" weeks: "weeks" → "w" Month: "months" → "M" quarters: "quarters" → "Q" Year: "years" → "y"
Click on the links to view all timing unit.
Example
// Return 2021/03/26 minus 2021/03/01.Timing Unit "days" means counting in "days".
Formula: DATETIME_DIFF("2021/03/26", "2021/03/01", "days")
Result: 25
// Return 2021/03/01 minus 2021/03/26.Timing Unit "days" means counting in "days".
Formula: DATETIME_DIFF("2021/03/01", "2021/03/26", "days")
Result: -25
// return {cutoff} minus TODAY().The following field {deadline} is of date type and the cell value is 2021/03/28. TODAY() returns the value 2021/03/30.Timing Unit "hours" means counting in "hours".
Formula: DATETIME_DIFF({ deadline }, TODAY(), "hours")
Result: -48
WORKDAY()
Return the date a number of business days after the start date.
WORKDAY(startDate, numDays, [holidays])
Parameter Description
startDate: indicates the start date. numDays: The number of working days after the start date that you specify, expressed as a positive value. For example, the number "1" represents the date one business day after the start date, as shown in Example 1. holidays: not required. Is a specific date to be removed from the calendar, such as a holiday.The input format is yyyy-mm-dd. Multiple dates are separated by commas.
The working day of this function does not include weekends and specific days you specify.
Example
// Return the date 1 business day after 2021/03/01.
Formula: WORKDAY("2021/03/01", 1)
Result: 2021/03/02
// Return the date after one working day from 2021/03/01, and the specified date 2021-03-02 is deleted. The specified date is in the format yyyy-mm-dd.
Formula: WORKDAY("2021/03/01", 1, "2021-03-02")
Result: 2021/03/03
// Return the date 10 business days after {product start date}, with the specific date removed. The following field {Product start date} is of date type and cell value 2021/03/01.
Formula: WORKDAY({start date}, 10, "2021-03-02, 2021-03-08, 2021-03-09")
Result: 2021/03/18
WORKDAY_DIFF()
Count the number of working days between two dates (plus or minus).
WORKDAY_DIFF(startDate, endDate, [holidays])
Parameter Description
startDate: indicates the start date. endDate: indicates the end date. If the start date is later than the deadline, a negative number will appear. holidays: not required. Is a date to remove from the work calendar, such as a holiday. The input format is yyyy-mm-dd. Multiple dates are separated by commas.
This function counts the number of working days between the start and end dates, excluding weekends and specific dates you specify.
Example
// Calculate the number of working days between 2021/03/01 and 2021/03/02.
Formula: WORKDAY_DIFF("2021/03/01", "2021/03/02")
Result: 2
// Calculate the number of working days between 2021/03/01 and 2021/03/02.
Formula: WORKDAY_DIFF("2021/03/02", "2021/03/01")
Result: -2
// Calculate the number of working days between 2021/03/01 and 2021/03/07.Of these, 2021/03/06 and 2021/03/07 are weekends.
Formula: WORKDAY_DIFF("2021/03/01", "2021/03/07")
Result: 5
// Calculate the number of working days between 2021/03/01 and 2021/03/07.And remove the specific date.The following fields {Product launch date} and {product launch date} are of date type and have cell values 2021/03/01 and 2021/03/07, respectively.
Formula: WORKDAY_DIFF({product launch date}, {product launch date}, "2021-03-02, 2021-03-03")
Result: 3
IS_AFTER()
Compares whether date 1 is later than date 2, returns true (true) if it is later, false (false) otherwise.
IS_AFTER(date1, date2)
Parameter Description
date1: is the date1. date2: is the date2. Date can be an input parameter, see use Case 1; Date can also be a column that references a date type, as shown in Use Case 2.
Within the cell true and false are represented by checked and unchecked.
Example
// Compare whether 2021/03/26 is later than 2021/03/25.
Formula: IS_AFTER("2021/03/26", "2021/03/25")
Result: TRUE
// Compare whether {deadline} is later than TODAY().The following field {deadline} is of date type and the cell value is 2021/03/26, and the following function TODAY() represents the value 2021/03/25.
Formula: IS_AFTER({ deadline }, TODAY())
Result: TRUE
// Compare whether {deadline} is later than 2021/03/25.The following field {deadline} is of date type and the cell value is 2021/03/26.
Formula: IS_AFTER({ deadline }, "2021/03/25")
Result: TRUE
IS_BEFORE()
Compares whether date 1 is earlier than date 2, returns true; if it is earlier, false otherwise.
IS_BEFORE(date1, date2)
Parameter Description
date1: is the date1. date2: is the date2.
Date can be an input parameter, see Use Case 1; Date can also be a column that references a date type, as shown in Use Case 2. Within the cell true and false are represented by checked and unchecked.
Example
// Compare whether 2021/03/25 is earlier than 2021/03/26.
Formula: IS_BEFORE("2021/03/25", "2021/03/26")
Result: TRUE
// Compare whether {deadline} is earlier than TODAY().The following field {deadline} is of date type and has cell value 2021/03/25, and the following function TODAY() is of date type and has cell value 2021-03-26.
Formula: IS_BEFORE({ deadline }, TODAY())
Result: TRUE
// Compare whether {deadline} is earlier than 2021/03/26.The following field {deadline} is of date type and the cell value is 2021/03/25.
Formula: IS_BEFORE({ deadline }, "2021/03/26")
Result: TRUE
IS_SAME()
Compares whether date 1 equals date 2 and returns true; if so, false otherwise.
IS_SAME(date1, date2, [units])
Parameter Description
date1: is the date1. date2: is the date2. units: This field is optional. It is the unit of time for comparison. For example, to compare whether two dates are equal, you can compare them all the way to minutes.
Date can be an input parameter, see Use Case 1; Date can also be a column that references a date type, as shown in Use Case 4. Within the cell true and false are represented by checked and unchecked.
Example
// Compare whether 2021/03/30 is equal to 2021/03/30.
Formula: IS_SAME("2021/03/30", "2021/03/30")
Result: TRUE
// Compare 2021/03/30 and 2021/04/30 for equality, up to the year.
Formula: IS_SAME("2021/03/30", "2021/04/30", "years")
Result: TRUE
// Compare 2021/03/30 and 2021/04/30 for equality, until month.
Formula: IS_SAME("2021/3/30", "2021/04/30", "months")
Result: FALSE
// Compare {due time} and {completion time} for equality, up to the number of days.The following fields {due time} and {completion time} are of date type and have cell values 2021/03/30.
Formula: IS_SAME({deadline}, {completion time}, "days")
Result: TRUE
// Compare 2021/03/30 and {completion time} for equality, up to the number of days.The following field {completion time} is of date type and the cell value is 2021/03/30.
Formula: IS_SAME("2021/03/30", {completion time}, "days")
Result: TRUE
DATETIME_FORMAT()
Formats the date as text in a custom form.
DATETIME_FORMAT(date, specified_output_format)
Parameter Description
date: indicates the date to be formatted. specified_output_format: is the selected format specifier.For example, a specifier could be:
DD-MM-YYYY indicates day-month-year, as shown in Example 1. YYYY/MM/DD indicates year/month/day, as shown in Example 2. MM.DD indicates month.day , as shown in Example 3.
When formatted, the date becomes a string of text.
See the link below for the date format specifiers supported by this function. Click on the links to view date format specifier.
Example
// Format 2021-03-30 as the text "30-03-2021".
Formula: DATETIME_FORMAT("2021-03-30", "DD-MM-YYYY")
Result: "30-03-2021"
// Format 2021-03-30 as the text "2021/03/30".
Formula: DATETIME_FORMAT("2021-03-30", "YYYY/MM/DD")
Result: "2021/03/30"
// Format 2021-03-30 as the text "03.30".
Formula: DATETIME_FORMAT("2021-03-30", "MM.DD")
Result: "03.30"
// Format TODAY() as the text "30-03-2021".The following function TODAY() is of date type and has a cell value of 2021-03-30.
Formula: DATETIME_FORMAT(TODAY(), "DD-MM-YYYY")
Result: 30-03-2021
DATETIME_PARSE()
Converts text to a structured date type.
DATETIME_PARSE(date, [input_format])
Parameter Description
date: is the text to be formatted as a date. input_format: Non-required. This parameter is a date formatting specifier.For text date content that is not recognized by the system, you can interpret it yourself as a structured date. See example 2
See the links below for date format specifiers and locales supported by this function. Click on the links to view date format specifier.
Example
// Format the text "20210330" as the date 2021/03/30.
Formula: DATETIME_PARSE (" 20210330 ")
Result: 2021/03/30
// Format the text "30 03 2021 18:00" as the date 2021/03/30 18:00.
Formula: DATETIME_PARSE("30 03 2021 18:00", "DD MM YYYY HH:mm")
Result: 2021/03/30 18:00
// Format the text "30 March 2021 18:00 "as the date 2021/03/30 18:00.
Formula: DATETIME_PARSE("30 March 2021 18:00 ", "DD MM month YYYY year HH:mm ")
Result: 2021/03/30 18:00
DATESTR()
Format the date as "year-month-day" text (fixed format: YYYY-MM-DD)
DATESTR(date)
Parameter Description
date: indicates the date to be formatted.
When formatted, the date becomes a string of text and no longer has the attributes of date data.
Example
// Format the date 2021/03/30 as text "2021-03-30".
Formula: DATESTR (" 2021/03/30 ")
Result: "2021-03-30"
// Format {start time} as the text "2021-03-30".The following field {start time} is of date type and the cell value is 2021/03/30.
Formula: DATESTR({start time})
Result: "2021-03-30"
TIMESTR()
Format the date as "hour: minute: second" text (fixed format: HH:mm:ss)
TIMESTR(date)
Parameter Description
date: indicates the date to be formatted.
When formatted, the date becomes a string of text and no longer has the attributes of date data.
Example
// Format NOW() as the text "00:15:31".The following function NOW() is of date type and the cell value is 2021/03/31 00:15.
Formula: TIMESTR(NOW())
Result: "00:15:31"
YEAR()
Returns the four-digit year corresponding to the specified date.
YEAR(date)
Parameter Description
date: indicates the specified date.
Example
// Return the four-digit year corresponding to 2021/03/31.
Formula: YEAR(" 2021/03/31 ")
Result: 2021
MONTH()
Returns the month corresponding to the specified date.
MONTH(date)
Parameter Description
date: indicates the specified date.
This function outputs an integer between 1 (January) and 12 (December).
Example
// Return the month corresponding to 2021/03/31.
Formula: the MONTH (" 2021/03/31 ")
Result: 3
WEEKDAY()
Returns the day of the week corresponding to the specified date.
WEEKDAY(date, [startDayOfWeek])
Parameter Description
date: indicates the specified date. startDayOfWeek: Non-required. It is the start time of a week. By default, it starts from Sunday (0 on Sunday).You can also set the start time to "Monday".
The value of this function is an integer between 0 and 6.
Example
// Return the day of the week 2021/03/31.
Formula: WEEKDAY(" 2021/03/31 ")
Result: 3
// Return TODAY() to the day of the week.The following function TODAY() is of date type and the cell value is 2021/03/31.
Formula: WEEKDAY(TODAY())
Result: 3
WEEKNUM()
Returns the week of the year corresponding to the specified date.
WEEKNUM(date, [startDayOfWeek])
Parameter Description
date: indicates the specified date. startDayOfWeek: date: indicates the specified date. It is the start time of a week. By default, it starts from Sunday (0 on Sunday). You can also set the start time to "Monday".
This function outputs an integer value.For example, 6 means the date falls in the sixth week of the year.
Example
// Return 2021/03/31 to the week of the year.
Formula: WEEKNUM(" 2021/03/31 ")
Result: 13
// Return 2021/03/31 to the week of the year.Set the start time to "Monday".
Formula: WEEKNUM("2021/03/31", "Monday")
Result: 13
// Return TODAY() to the week of the year.The following function TODAY() is of date type and the cell value is 2021/03/31.
Formula: WEEKNUM(TODAY())
Result: 13
DAY()
Returns the date of the month in which the specified date belongs. The value is an integer ranging from 1 to 31.
DAY(date)
Parameter Description
date: indicates the specified date. For example, the number 1 indicates that the date falls on the first day of the month.
Example
// Return the date of the month 2021/03/31 belongs to.
Formula: DAY (" 2021/03/31 ")
Result: 31
// Return the date of the month to which {completion date} belongs.The following fields {completion date} are of date type and the cell value is 2021/03/31.
Formula: DAY({completion date})
Result: 31
HOUR()
Returns the time corresponding to the specified date. The output format is an integer between 0 (12:00 am) and 23 (11:00 pm).
HOUR(date)
Parameter Description
date: indicates the specified date.
Example
// Return the time corresponding to {punch time}.The following field {Clocked time} is of date type and the cell value is 2021/03/31 10:30.
Formula: HOUR({clock time})
Result: 10
MINUTE()
Returns the number of minutes corresponding to the specified date. The output format is an integer between 0 and 59.
MINUTE(date)
Parameter Description
date: indicates the specified date.
Example
// Return the number of minutes corresponding to {clocking time}.The following fields {Clocked time} are of date type and the cell value is 2021/03/31 10:30.
Formula: MINUTE({punch in time})
Result: 30
SECOND()
Returns the number of seconds on the specified date as an integer between 0 and 59.
SECOND(date)
Parameter Description
date: indicates the specified date.
Example
// Return the number of seconds corresponding to {clocked time}.The following field {Clocked time} is of date type and the cell value is 2021/03/31 10:30.
Formula: SECOND({Clocking time})
Result: 0
SET_LOCALE()
Sets a specific locale for a specified date and time.
SET_LOCALE(date, locale_modifier)
Parameter Description
date: indicates the specified date. locale_modifier: is the language environment specifier.
This function must be used in conjunction with
DATETIME_FORMAT().Click on the links to view support language environment specifier.
Example
// Set the Chinese locale for NOW().
Formula: DATETIME_FORMAT(SET_LOCALE(NOW(), "zh-cn"), "LLLL")
Result: 5:21 PM on Wednesday, March 31, 2021
SET_TIMEZONE()
Sets a specific time zone for a specified date.
SET_LOCALE(date, locale_modifier)
Parameter Description
date: indicates the specified date. locale_modifie: indicates the time zone specifier.For example, "8" represents East Ward 8, and "-2" represents West Ward 2.
This function must be used in conjunction with DATETIME_FORMAT().
Example
// Set east 8 zone for NOW().
Formula: DATETIME_FORMAT(SET_TIMEZONE(NOW(), 8), "M/D/YYYY h:mm")
Result: 3/31/2021 5:38
CREATED_TIME()
Returns the date and time when this record was created.
CREATED_TIME()
Parameter Description
This function requires no parameters.
Example
// Return the date and time when the record was created.
Formula: CREATED_TIME ()
Result: 2021/03/22 14:25
LAST_MODIFIED_TIME()
Returns the time of the last modification in the cell of each row.
LAST_MODIFIED_TIME([{field1},{field2}, …])
Parameter Description
Note ①: When the cell values of the fields (Formula, Magic lookup, Autonumber, Created time, Creator, Last modified by) of the calculation type change, the system will not return the modification time of these fields.
You can specify one or more columns if you care only about the update times of cells in a particular column, as shown in examples 2 and 3.
Example
// Returns the time of the last modification in the cell of each row.The first line returns an update time of 18:30, 2021/03/31.
Formula: LAST_MODIFIED_TIME ()
Result: 2021/03/31 18:30
// Return the cell update time for {project progress}.
Formula: LAST_MODIFIED_TIME({project progress})
Result: 2021/03/31 18:37
// Returns the cell update times for {project progress} and {task assignment}.
Formula: LAST_MODIFIED_TIME({project progress}, {task assignment})
Result: 2021/03/31 18:41