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 theDate 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 theDate 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 betweenDate 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 theDate 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 theDate 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:
Format | Description | Examples |
---|---|---|
YY | The year, from 00 to 99. | 03, 17 |
YYYY | The year as a four-digit number. | 2003, 2017 |
M | The month, from 1 to 12. | 6, 12 |
MM | The month as a two-digit number. | 06, 12 |
MMM | The abbreviated name of the month. | Jun, Dec |
MMMM | The full name of the month. | June, December |
D | The day of the month, from 1 to 31. | 1, 15 |
DD | The day of the month, from 01 to 31. | 01, 15 |
d | The day of the week, from 0 to 6, with 0 as Sunday. | 0, 5 |
dd | The short name of the day of the week. | Su, Fr |
ddd | The abbreviated name of the day of the week. | Sun, Fri |
dddd | The full name of the day of the week. | Sunday, Friday |
H | The hour, using a 24-hour clock from 0 to 23. | 1, 23 |
HH | The hour, using a 24-hour clock from 00 to 23. | 01, 23 |
h | The hour, using a 12-hour clock from 1 to 12. | 1, 11 |
hh | The hour, using a 12-hour clock from 01 to 12. | 01, 11 |
m | The minute, from 0 to 59. | 5, 50 |
mm | The minute, from 00 to 59. | 05, 50 |
s | The second, from 0 to 59. | 3, 30 |
ss | The second, from 00 to 59. | 03, 30 |
A | Before or after midday in uppercase. | AM, PM |
a | Before 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 theDate 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 toDate Picker
.=FROMNOW({Reservation Date})
will return a positive number ifReservation 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 theDate 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 ifReservation Date
is in the future of or later thanCurrent 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 ifDate of Birth
is in the past or earlier thanCurrent 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 ifAppointment
is the same day asCurrent 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 theDate 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 theDate 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 fromDate Picker
up to now.=TONOW({Date of Birth})
will return a positive number ifDate 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 theDate 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 theDate 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.
— 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.=WORKDAY("Monday, January 1, 2024",1,"2024-01-02","2024-01-03")
=TEXT(WORKDAY({Date Picker},7),"YYYY-MM-DD")
will return the date, 7 workdays fromDate 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 theDate Picker
value’s year.=YEAR(TODAY())
will return the current date’s year.=YEAR(NOW())
will return the current date’s year.
Send Comment:
4 Comments:
120 days ago
I have this formula =IF({57}="Hired",TODAY(),"") which return the date of hire. However it return it in US date format and I need it in UK DD/MM/YYY how can I change the format? Thanks
133 days ago
I'm trying to use DATEADD, but it's not (or is no longer) a function. IDK
More than a year ago
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!
More than a year ago
Looking for a date calculator to total date columns