Date and time functions — TableDI manual

A date column has to be the Date field type first, or be normalised out of text with date / dateformat, before the rest of these work. To total by month use dateformat(column, "yyyy-MM"); for days between two dates use days; for a date table that grows by itself use datetime.

Note

Conventions: reference a column by its code (AA6); function names are case-insensitive; text goes in double quotes; conditions accept and / or. "In the result column, enter" means typing the formula into a new column's formula bar. The wording below is the same as the in-app function help.

date

Formula Description

Converts a date string to a date value, used for time offset calculations

Formula Syntax

date("date_string", "matching_time_format")

date("date_string")+time_offset

date_string: For example: "2023-01-11 12:11:11"
matching_time_format: Default is "yyyy-mm-dd hh:mm:ss"; "yyyy-ww" represents the ww-th week
time_offset: In seconds, can be negative
All parameters can use data columns, i.e., the CODE at the top of each table data column, e.g., AA1

date("2022-03-17"); // Date
date("2023-01-11 12:11:11", "yyyy-MM-dd HH:mm:ss"); // Precise to seconds
date("2023-11", "yyyy-MM"); // First day of November at 00:00
date("2022-03", "yyyy-ww"); // First day of the 3rd week at 00:00

dateformat

Formula Description

Converts a time string according to the specified time format.

Formula Syntax

dateformat("time_value / time_column_number", "time_format")

time_value: For example: "2022-12-30"
time_column: Data column containing time values
time_format:
- yyyy-MM-dd HH:mm:ss corresponds to year-month-day hour-minute-second;
- ww corresponds to the ww-th week of the year
- Any combination can be used, e.g., MM-dd returns month-day

datetime

No detailed description ships in the app for this one — the function help in the formula editor is authoritative.

datedif

Formula Description

Calculates the number of days, months, or years between two dates.

Formula Syntax

In the result column, enter: DATEDIF(start_date, end_date, unit);

start_date: Required. The start date of the period.
- Can be entered in various formats:
- As a text string in quotes (e.g., "2001/1/30")
- As a serial number (e.g., 36921, which represents January 30, 2001, in the 1900 date system)
- As the result of other formulas or functions (e.g., DATEVALUE("2001/1/30"))
end_date: Required. The end date of the period.
unit: Required. The type of information to return:
- "Y" - The number of complete years in the period.
- "M" - The number of complete months in the period.
- "D" - The number of days in the period.
- "MD" - The difference between the days in start_date and end_date. The months and years of the dates are ignored.
- "YM" - The difference between the months in start_date and end_date. The days and years of the dates are ignored.
- "YD" - The difference between the days of start_date and end_date. The years of the dates are ignored.

days

Formula Description

Returns the number of days between two dates.

Formula Syntax

In the result column, enter: DAYS(date / column number, date / column number);

timestamptodate

Formula Description

Converts a standard timestamp to a standard date

Formula Syntax

In the result column, enter: timestamptodate(timestamp / column number)

Timestamp: 10 or 13 digits, e.g., 1675851551

unixtimestamp

Converts a time string according to the specified time format

month

Formula Description

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

Formula Syntax

In the result column, enter: MONTH(column number)

The MONTH function syntax has the following arguments:

Column number is required. The date of the month you want to find. Dates should be entered by using the DATE function, or as results of other formulas or functions.
For example, use DATE(2008,5,23) to return May 23, 2008. Problems can occur if dates are entered as text.

dayofmonth

Formula Description

Returns the day of the month for a specified date.

Formula Syntax

In the result column, enter: DAYOFMONTH(date / column number)

dayofyear

Formula Description

Returns the day of the year for a specified date.

Formula Syntax

In the result column, enter: DAYOFYEAR(date / column number)

dayofweek

Formula Description

Returns the day of the week index for a date, where Monday is 1 and Sunday is 7.

Formula Syntax

In the result column, enter: dayofweek(date / data_column, [returnType])

date/data_column: Represents the input date, can be a specific date value or a data column containing date values.
returnType: Specifies the type of return value, with three options, default is 2
- 1: Returns 1-7 (Sunday-Saturday)
- 2: Returns 1-7 (Monday-Sunday)
- 3: Returns 0-6 (Monday-Sunday)

dayname

Formula Description

Returns the name of the day of the week for a given date, such as "Sunday", "Monday", etc.

Formula Syntax

In the result column, enter: DAYNAME(date / column_number)

weekofyear

Formula Description

Returns the week number of the year for a specified date.

Formula Syntax

In the result column, enter: WEEKOFYEAR(date / column number, [mode])

date: The date
mode: Optional, determines
- Whether the week starts on Monday or Sunday
- How to handle weeks that span across years

mode	First day of week	Range	First week calculation
0	Sunday	0-53	The first Sunday of the year is the start of week 1. Days before are week 0
1	Monday	0-53	If January 1 is more than 3 days from the first Monday, it's week 1. Otherwise, it's week 0
2	Sunday	1-53	The first Sunday of the year is the start of week 1. Days before are week 5x of the previous year
3	Monday	1-53	If January 1 is more than 3 days from the first Monday, it's week 1. Otherwise, it's week 5x of the previous year
4	Sunday	0-53	If January 1 is more than 3 days from the first Sunday, it's week 1. Otherwise, it's week 0
5	Monday	0-53	The first Monday of the year is the start of week 1. Days before are week 0
6	Sunday	1-53	If January 1 is more than 3 days from the first Sunday, it's week 1. Otherwise, it's week 5x of the previous year
7	Monday	1-53	The first Monday of the year is the start of week 1. Days before are week 5x of the previous year

Generally, you don't need to specify the mode; the default is 3.

yearweek

Formula Description

Returns the year and week number for a specified date.

Formula Syntax

In the result column, enter: YEARWEEK(date / column number, [mode])

date: The date
mode: Optional, determines
- Whether the week starts on Monday or Sunday
- How to handle weeks that span across years

mode	First day of week	First week calculation
0,2	Sunday	The first Sunday of the year is the start of week 1. Days before are week 5x of the previous year
1,3	Monday	If January 1 is more than 3 days from the first Monday, it's week 1. Otherwise, it's week 5x of the previous year
4,6	Sunday	If January 1 is more than 3 days from the first Sunday, it's week 1. Otherwise, it's week 5x of the previous year
5,7	Monday	The first Monday of the year is the start of week 1. Days before are week 5x of the previous year

isoweeknum

Formula Description

Returns the ISO week number of the year for a given date.

The ISO week number is a way of representing the week number of a year as defined by the International Organization for Standardization (ISO).

ISO week numbers divide a year into 52 or 53 weeks, where the first week starts on a Monday and includes the first Thursday of the year. This means that the first week of a year may start in the last few days of the previous year and extend into the first few days of the next year.

ISO week numbers are primarily used for statistical and calculation purposes, especially in business and production fields, to determine date ranges for specific weeks.

Formula Syntax

ISOWEEKNUM(date / column number)

The ISOWEEKNUM function syntax has the following arguments:

Date is required. The date is a datetime code that Excel uses for date and time calculations.

lastdayofmonth

Formula Description

Returns the last day of the month for the specified date.

Formula Syntax

In the result column, enter: LASTDAYOFMONTH(date / column number)

hour

Formula Description

Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Formula Syntax

In the result column, enter: HOUR(time / column number)

The HOUR function syntax has the following arguments:

Column number is required. The time that contains the hour you want to find. Times can be entered in several ways:
- As a text string in quotes (e.g., "6:45 PM")
- As a decimal number (e.g., 0.78125 represents 6:45 PM)
- As a result of other formulas or functions (e.g., TIMEVALUE("6:45 PM"))
Enter HOUR(0-1) as a decimal to get the percentage of a 24-hour period

To calculate 75% of 24 hours: HOUR(0.75)

minute

Formula Description

Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

Formula Syntax

In the result column, enter: MINUTE(column number)

The MINUTE function syntax has the following arguments:

Column number is required. A time that contains the minutes you want to find. Times can be entered in several ways:
- As a text string in quotes (e.g., "6:45 PM")
- As a decimal number (e.g., 0.78125 represents 6:45 PM)
- As a result of other formulas or functions (e.g., TIMEVALUE("6:45 PM"))

second

Formula Description

Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.

Formula Syntax

In the result column, write: SECOND(column number)

The SECOND function syntax has the following arguments:

Column number is required. A time that contains the seconds you want to find. Time values can be entered in several ways:
- As a text string in quotes (e.g., "6:45 PM")
- As a decimal number (e.g., 0.78125 represents 6:45 PM)
- As a result of other formulas or functions (e.g., TIMEVALUE("6:45 PM"))