Advanced Formula: Date Functions

July 5, 2024

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 to 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:

Function Description
DATEADD Adds a specified number of units to a particular date.
DATESTR Returns date in YYYY-MM-DD format.
DATETIME_DIFF Returns the difference between two dates.
DATETIME_FORMAT Changes the format of a date & time.
DAY Returns the date’s day of the month.
FROMNOW Counts the number of days starting from now up to a given date.
HOUR Returns the hour value of a time.
IS_AFTER Determines if a date comes after another.
IS_BEFORE Determines if a date comes before another.
IS_SAME Determines if two dates are the same.
MINUTE Returns the minute value of a time.
MONTH Returns the date’s month.
NOW Returns the current date & time’s serial number.
SECOND Returns the second value of a time.
TIMESTR Returns a time in HH:MM:SS format.
TODAY Returns the current date.
TONOW Counts the number of days starting from a given date up to now.
WEEKDAY Returns the date’s day of the week.
WEEKNUM Returns the date’s week number.
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.
YEAR Returns the date’s year.

See also:

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 list of accepted date & time format specifiers:

FormatDescriptionExamples
YYThe year, from 00 to 99.03, 17
YYYYThe year as a four-digit number.2003, 2017
MThe month, from 1 to 12.6, 12
MMThe month as a two-digit number.06, 12
MMMThe abbreviated name of the month.Jun, Dec
MMMMThe full name of the month.June, December
DThe day of the month, from 1 to 31.1, 15
DDThe day of the month, from 01 to 31.01, 15
dThe day of the week, from 0 to 6, with 0 as Sunday.0, 5
ddThe short name of the day of the week.Su, Fr
dddThe abbreviated name of the day of the week.Sun, Fri
ddddThe full name of the day of the week.Sunday, Friday
HThe hour, using a 24-hour clock from 0 to 23.1, 23
HHThe hour, using a 24-hour clock from 00 to 23.01, 23
hThe hour, using a 12-hour clock from 1 to 12.1, 11
hhThe hour, using a 12-hour clock from 01 to 12.01, 11
mThe minute, from 0 to 59.5, 50
mmThe minute, from 00 to 59.05, 50
sThe second, from 0 to 59.3, 30
ssThe second, from 00 to 59.03, 30
ABefore or after midday in uppercase.AM, PM
aBefore 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 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:

TypeNumber Returned
1 or omittedNumbers 1 (Sunday) through 7 (Saturday).
2Numbers 1 (Monday) through 7 (Sunday).
3Numbers 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.
Academy Logo

Introduction to Data Management

Adding Columns and Action Buttons to Tables

Go to course :Adding Columns and Action Buttons to Tables
lesson-3 - image
Contact Support:

Our customer support team is available 24/7 and our average response time is between one to two hours.
Our team can be contacted via:

Support Forum: https://www.jotform.com/answers/

Contact Jotform Support: https://www.jotform.com/contact/

Send Comment:

Jotform Avatar
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Comments:

  • Naomi Thomson - Profile picture
  • Joe Wetzel - Profile picture
  • Alan Bredenhorst - Profile picture
  • Sue Ann Churchill - Profile picture