Advanced Formula: Date Functions

January 12, 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: NOW runs only once when the page loads or when the formula is recalculated so you may need to refresh the Tables to update the results.

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: TODAY runs only once when the page loads or when the formula is recalculated so you may need to refresh the Tables to update the results.

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.
Was this guide helpful?
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/

Jotform for Beginners

Get more done with powerful, easy-to-use online forms. Learn how in this helpful, free guide from Jotform.

Download the Book
Jotform for Beginners

Send Comment:

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

Comments:

  • Alan Bredenhorst
  • Sue Ann Churchill
Access powerful form features with Jotform's free plan.
Access powerful form features with Jotform's free plan. Sign Up for Free!
Make an online form in minutes with Jotform.
Make an online form in minutes with Jotform. Create a Free Form
Discover Jotform’s powerful online form features.
Discover Jotform’s powerful online form features. View Available Plans
Jotform for Beginners.

Get more done with powerful, easy-to-use online forms.

Learn how in this helpful, free guide from Jotform.

Jotform for Beginners.
Download the Book