Advanced Formula: Date Functions

Jotform Tables’ Advanced Formula provides functions that allow you to retrieve, format, and perform operations on date & time in Jotform Tables.

Notes:

• Jotform Tables converts dates to sequential serial numbers so they can be used in calculations. By default, 1900-01-01 is 1, and 2025-01-21 is 45678 because it is 45,678 days from January 1, 1900.
• Function parameters labeled datetime accept Date Picker or Date & Time column type values only.
• Some function parameters accept date strings in various formats (i.e., YYYY-MM-DD, M/D/YYYY, MM/DD/YYYY).

Here’s the list of date and time functions you can use in Jotform Tables:

See also:

• Operators
• Text Functions
• Logical Functions
• Numeric Functions
• Array and Record Functions

DATEADD

Adds a specified number of units to a particular date.

Syntax:

DATEADD(date,number,unit)

The accepted units are

• years
• months
• weeks
• days
• hours
• minutes
• seconds

Examples:

• =DATEADD("06/20/2025",3,"days") will return Jun 23, 2025.
• =DATEADD("6/20/2025",-1,"year") will return Jun 20, 2024.
• =DATEADD({Date Picker},1,"week") will add 1 week to the Date Picker column’s value.
• =DATEADD(TODAY(),-48,"hours") will subtract 48 hours to the current date.
• =DATEADD(TEXT("June 20, 2025","MM/DD/YYYY"),3,"days") will return Jun 23, 2025.

DATESTR

Converts datetime into YYYY-MM-DD format. The datetime parameter accepts Date Picker or Date & Time column type values only. For other value types and return formats, see TEXT.

Syntax:

DATESTR(datetime)

Examples:

• =DATESTR({Date Picker}) will return the Date Picker column’s value in YYYY-MM-DD format, like 2030-06-19.
• =DATESTR(TODAY()) will return #VALUE!.
• =DATESTR(NOW()) will return #VALUE!.

DATETIME_DIFF

Returns the difference between two dates in specified units (default: hours).

Syntax:

DATETIME_DIFF(date1,date2[,unit])

The accepted units are:

• years
• months
• days
• hours

Examples:

• =DATETIME_DIFF({Date of Birth},TODAY(),"years") will return the age or the number of years between Date of Birth (Date Picker) and today.
• =DATETIME_DIFF("01/20/1900","01/22/1900","days") will return 2.
• =DATETIME_DIFF("01/22/1900","01/20/1900","hours") will return -48.

DATETIME_FORMAT

Changes the way a datetime appears by applying format. The datetime parameter accepts Date Picker or Date & Time column type values only. For other value types and return formats, see TEXT.

Syntax:

DATETIME_FORMAT(datetime,format)

Examples:

• =DATETIME_FORMAT({Date Picker},"YYYY-MM-DD HH:mm:ss") will return the Date Picker column’s value in “YYYY-MM-DD HH:mm:ss” format, like 1900-01-20 18:30:00.
• =DATETIME_FORMAT({Date Picker},"MMMM D, YYYY hh:mm a") will return the Date Picker column’s value in “MMMM D, YYYY hh:mm a” format, like November 9, 1999 11:20 pm.

Here’s a table of accepted date & time format specifiers:

Format	Description	Examples
YY	The year, from 00 to 99.	03, 17
YYYY	The year as a four-digit number.	2003, 2017
M	The month, from 1 to 12.	6, 12
MM	The month as a two-digit number.	06, 12
MMM	The abbreviated name of the month.	Jun, Dec
MMMM	The full name of the month.	June, December
D	The day of the month, from 1 to 31.	1, 15
DD	The day of the month, from 01 to 31.	01, 15
d	The day of the week, from 0 to 6, with 0 as Sunday.	0, 5
dd	The short name of the day of the week.	Su, Fr
ddd	The abbreviated name of the day of the week.	Sun, Fri
dddd	The full name of the day of the week.	Sunday, Friday
H	The hour, using a 24-hour clock from 0 to 23.	1, 23
HH	The hour, using a 24-hour clock from 00 to 23.	01, 23
h	The hour, using a 12-hour clock from 1 to 12.	1, 11
hh	The hour, using a 12-hour clock from 01 to 12.	01, 11
m	The minute, from 0 to 59.	5, 50
mm	The minute, from 00 to 59.	05, 50
s	The second, from 0 to 59.	3, 30
ss	The second, from 00 to 59.	03, 30
A	Before or after midday in uppercase.	AM, PM
a	Before or after midday in lowercase.	am, pm

DAY

Returns the date’s day of the month, ranging from 1 to 31.

Syntax:

DAY(date)

Examples:

• =DAY("1900-01-20") will return 20.
• =DAY("June 19, 2024") will return 19.
• =DAY({Date Picker}) will return the Date Picker value’s day of the month, like 28.
• =DAY(TODAY()) will return the current date’s day of the month.

FROMNOW

Counts the number of days starting from now up to a given datetime. The datetime parameter accepts Date Picker or Date & Time column type values only.

Syntax:

FROMNOW(datetime)

Examples:

• =FROMNOW({Date Picker}) will return the number of days from now up to Date Picker.
• =FROMNOW({Reservation Date}) will return a positive number if Reservation Date is in the future.

HOUR

Returns the time’s hour value, ranging from 0 (12 midnight) to 23 (11:00 PM). The HOUR function also works with numbers or datetime serial numbers.

Syntax:

HOUR(time|number)

Examples:

• =HOUR("12 am") will return 0.
• =HOUR("11:15 PM") will return 23.
• =HOUR("16:20") will return 16.
• =HOUR("18:42:59") will return 18.
• =HOUR(TIMESTR({Date Picker}) will return the Date Picker time’s hour value, like 23.
• =HOUR(NOW()) will return the current time’s hour.
• =HOUR(0.5) will return 12.
• =HOUR(2.75) will return 18.

IS_AFTER

Returns TRUE if datetime1 is after datetime2 and FALSE otherwise. The datetime parameters accept Date Picker or Date & Time column type values only.

Syntax:

IS_AFTER(datetime1,datetime2)

Examples:

• =IS_AFTER({Reservation Date},{Current Date}) will return TRUE or 1 if Reservation Date is in the future of or later than Current Date.
• =IS_AFTER({Date Picker},TODAY()) will return #VALUE!.
• =IS_AFTER({Date Picker},NOW()) will return #VALUE!.

IS_BEFORE

Returns TRUE if datetime1 is before datetime2 and FALSE otherwise. The datetime parameters accept Date Picker or Date & Time column type values only.

Syntax:

IS_BEFORE(datetime1,datetime2)

Examples:

• IS_BEFORE({Date of Birth},{Current Date}) will return TRUE or 1 if Date of Birth is in the past of or earlier than Current Date.
• =IS_BEFORE({Date Picker},TODAY()) will return #VALUE!.
• =IS_BEFORE({Date Picker},NOW()) will return #VALUE!.

IS_SAME

Compares two dates up to a unit and returns TRUE if they are identical and FALSE otherwise. The datetime parameters accept Date Picker or Date & Time column type values only.

Syntax:

IS_SAME(datetime1,datetime2,unit)

The accepted units are:

• years
• months
• days
• hours
• minutes

Examples:

• =IS_SAME({Appointment},{Current Date},"days") will return TRUE or 1 if Appointment is the same day as Current Date.
• =IS_SAME({Date Picker},TODAY(),"days") will return #VALUE!.
• =IS_SAME({Date Picker},NOW(),"days") will return #VALUE!.

MINUTE

Returns the time’s minute value, ranging from 0 to 59.

Syntax:

MINUTE(time|number)

Examples:

• =MINUTE(NOW()) will return the current time’s minute value, like 23.
• =MINUTE("11:15 PM") will return 15.
• =MINUTE("18:42:59") will return 42.
• =MINUTE(0.02084) will return 30.
• =MINUTE({Date Picker}) will return the Date Picker time’s minute value, like 59.

MONTH

Returns the date’s month, ranging from 1 (January) to 12 (December).

Syntax:

MONTH(date)

Examples:

• =MONTH("January 1, 1900") will return 1.
• =MONTH("2030-12-25") will return 12.
• =MONTH(TODAY()) will return the current date’s nth month, like 7.

NOW

Returns the current date & time’s serial number or the number of days since January 1, 1900. The NOW function has no arguments.

Syntax:

NOW()

Examples:

• =NOW() will return the current date & time’s serial number, like 44769.670104167.
• =TEXT(NOW(),"YYYY-MM-DD HH:MM:SS") will return the current date and time in “YYYY-MM-DD HH:MM:SS” format, like 2022-07-28 10:19:31.
• =HOUR(NOW()) will return the current time’s hour.

SECOND

Returns the time’s second value, ranging from 0 to 59.

Syntax:

SECONDS(time|number)

Examples:

• =SECOND("15:30:45") will return 45.
• =SECOND(0.000175) will return 15.
• =SECONDS(NOW()) will return the current time’s second value, like 59.

TIMESTR

Returns the datetime value’s time in HH:MM:SS format. The datetime parameter accepts Date Picker or Date & Time column type values only. For other value types and return formats, see TEXT.

Syntax:

TIMESTR(datetime)

Examples:

• =TIMESTR({Date Picker}) will return the Date Picker value’s time, like 23:30:00.
• =TIMESTR(TODAY()) will return #VALUE!.
• =TIMESTR(NOW()) will return #VALUE!.

TODAY

Returns the current date in M/DD/YYYY format. The TODAY function has no arguments.

Syntax:

TODAY()

Examples:

• =TODAY() will return the current date, like 7/27/2022.
• =DAY(TODAY()) will return the current date’s day of the month, like 27.

TONOW

Counts the number of days starting from a given datetime up to now. The datetime parameter accepts Date Picker or Date & Time column type values only.

Syntax:

TONOW(datetime)

Examples:

• =TONOW({Date Picker}) will return the number of days from Date Picker up to now.
• =TONOW({Date of Birth}) will return a positive number if Date of Birth is in the past.

WEEKDAY

Returns the date’s day of the week, ranging from 1 (Sunday) to 7 (Saturday).

Syntax:

WEEKDAY(date[,type])

The optional type parameter determines the type of return value:

Type	Number Returned
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday).
2	Numbers 1 (Monday) through 7 (Sunday).
3	Numbers 0 (Monday) through 6 (Sunday).

Examples:

• =WEEKDAY("Sunday, July 3, 2022") will return 1.
• =WEEKDAY("Jul 4, 2022",2) will return 1.
• =WEEKDAY("2022-07-04",3) will return 0.
• =WEEKDAY({Date Picker}) will return the Date Picker value’s day of the week.
• =WEEKDAY(TODAY()) will return the current date’s day of the week.
• =WEEKDAY(NOW()) will return the current date’s day of the week.

WEEKNUM

Returns the date’s week number, ranging from 1 up to 53 weeks of the year.

Syntax:

WEEKNUM(date)

Examples:

• =WEEKNUM("Sunday, January 1, 2023") will return 1.
• =WEEKNUM("Feb 1, 2023") will return 5.
• =WEEKNUM("2023-12-31") will return 53.
• =WEEKNUM({Date Picker}) will return the Date Picker value’s week number.
• =WEEKNUM(TODAY()) will return the current date’s week number.
• =WEEKNUM(NOW()) will return #VALUE!.

WORKDAY

Returns the date’s serial number before or after a starting date, specified by the number of working days and an optional list of holidays in YYYY-MM-DD format.

Syntax:

WORKDAY(date,days[,holiday1,holiday2,...])

Examples:

• =WORKDAY("Monday, January 1, 2024",1) will return 45293 which is equivalent to Tuesday, January 2, 2024.
• =WORKDAY("Monday, January 1, 2024",1,"2024-01-02","2024-01-03") — one workday after January 1, 2024, excluding January 2 & 3 of the same year — will return 45295 which is equivalent to Thursday, January 4, 2024.
• =TEXT(WORKDAY({Date Picker},7),"YYYY-MM-DD") will return the date, 7 workdays from Date Picker, in YYYY-MM-DD format, like 2022-07-24.

YEAR

Returns the date’s year, ranging from 1900 to 9999.

Syntax:

YEAR(date)

Examples:

• =YEAR("2025-01-21") will return 2025.
• =YEAR("June 4, 1997") will return 1997.
• =YEAR("{Date Picker}) will return the Date Picker value’s year.
• =YEAR(TODAY()) will return the current date’s year.
• =YEAR(NOW()) will return the current date’s year.