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
January 12, 2023
Advanced Formula provides functions that allow you to retrieve, format, and perform operations on date & time in Jotform Tables.
Notes:
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:
Adds a specified number of units to a particular date.
Syntax:
DATEADD(date,number,unit)
The accepted units are:
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.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!.Returns the difference between two dates in specified units (default: hours).
Syntax:
DATETIME_DIFF(date1,date2[,unit])
The accepted units are:
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.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
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.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.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.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!.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!.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:
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!.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.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.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.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.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!.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.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.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.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!.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.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.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:
2 Comments:
Hi supporters,
Two questions:
1. I can't seem to get my formula right:
I want to calculate the amount of years between date of birth and starting date of a training our applicants take part in, stated in years in column 'V - Leeftijd bij start MDT traject'.
See example: Unique ID 23447, and column with Field ID: #input_115
2. Why does a Birth date (column Geboortedatum) result in the table in 'Invalid date' (f.e. Unique ID: 23425)
Thank you in advance!
Looking for a date calculator to total date columns