Advanced Formula: Date Functions

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},"Y-m-d H:i:s") will return the Date Picker column’s value in “Y-m-d H:i:s” format, like 1900-01-20 18:30:00.
• =DATETIME_FORMAT({Date Picker},"F j, Y g:i a") will return the Date Picker column’s value in “F j, Y g:i a” format, like November 29, 1999 11:20 pm.

For more information, see: Format String Examples

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.

Note: In order to recalculate the values, you should click on Edit Formula in the column menu and apply changes there.

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.

Note: In order to recalculate the values, you should click on Edit Formula in the column menu and apply changes there.

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.