Advanced Formula: Date Functions

April 18, 2023

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:

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},"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:

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

  • Alan Bredenhorst - Profile picture
  • Sue Ann Churchill - Profile picture