You might have a scenario where you need to capture the total time a user took to complete a form or the difference between the start and end times of an activity based on user input.
This is where the DATEDIFF() function comes in handy. It can be used in the Dynamic Value property of fields and calculates the difference between two dates.
DATEDIFF({{startDate}}, {{endDate}}, 'unit')
The function requires a startDate and an endDate date value and a unit of time specified to return the value in, i.e., years, months, days, hours, or seconds.
The only thing left now is when and how the start/end times are captured, which this article touches on.
TABLE OF CONTENTS
- Capturing dates and times from Date/Time fields
- Capturing dates and times when interacting with fields
- Date/Time Difference Example App
Capturing dates and times from Date/Time fields
When you need to get the difference between two Date/Time fields in a form, the DATEDIFF() function can be used, for example, in a text, numeric, or hidden field in the Dynamic Value property.
Let's assume the data names of two Date/Times fields are startDate and endDate, and we'd like to calculate the time difference in hours in a Text field. The formula in the Text field's Dynamic Value property would look like this:
DATEDIFF({{startDate}} , {{endDate}} , 'HH')
You'll notice the 'unit' specifier 'HH', which returns the value in hours. If you need it in a different unit of time, the following can be used:
- YY - whole years
- MM - whole months
- DD - whole days
- HH - whole hours
- MI - whole minutes
- SS - whole seconds
Capturing dates and times when interacting with fields
When you need to capture a date/time value when a user interacts with a field, for example, when a field gets a value and is no longer blank.
First, you'll need a function that returns a date/time value. Second, how to trigger and store the date/time value in a field when another field is interacted with.
Here are a few functions that return a date/time value depending on your needs would be:
- NOW()
- TODAY()
- UTCNOW()
- UTCTODAY()
Triggering a date/time value and formatting it in a field when another field has a value can be achieved using the following:
FORMAT-DATE(NOW({{dataname}}) , 'yyyy-MM-dd HH:mm:ss')
or
IF(NOTBLANK({{dataname}}) , FORMAT-DATE(NOW() , 'yyyy-MM-dd HH:mm:ss') , '')
Here's a breakdown of what the above functions are doing:
- NOW() returns the current local date reported by the device.
- NOW({{dataname}}) parameter returns the date when the field referenced dataname gets a value.
- FORMAT-DATE() formats the NOW() value to display the full date and time needed for the DATEDIFF() function.
- NOTBLANK({{dataname}}) returns a true if the field referenced dataname has a value, false if not.
- IF() returns one of two values based on whether the given condition is true or false.
| Most .NET Format strings/specifiers can work with our FORMAT-DATE() formula function for added customization of captured Dates and Times. |
Now, let's look at an example. Assume the data names of the first and last fields in a form are firstField and lastField, and we'd like to calculate the difference in minutes in a Text field.
We'll also need, ideally, a hidden field for each date capture before calculating the difference. Let's give these fields data names of firstFieldTime and lastfieldTime.
Formulas in the Dynamic Value properties of the two hidden fields and the field that calculates the difference would look like this:
firstFieldTime (hidden)
FORMAT-DATE(NOW({{firstField}}), 'yyyy-MM-dd HH:mm:ss')
lastFieldTime (hidden)
FORMAT-DATE(NOW({{lastField}}), 'yyyy-MM-dd HH:mm:ss')
timeDifference
DATEDIFF({{firstFieldTime}}, {{lastFieldTime}}, 'MI')
Date/Time Difference Example App
Within out Examples Catalog, there is a Date/Time Difference Example App that you can install and test out the functionality mentioned above.
Design Tools > Form Templates
The app consists of 4 pages with various fields to capture and display data differently.
- Introduction + Capture Start time of form
- Date Difference
- Time Difference
- Capture End time of form + Summary