Forms, fields and formulas

How do I check that the gap between two date-time fields is more than 1 hour?

Validate the end field against a helper field set to the start plus one hour, or use dateDiff in a step condition.

How-to

Short answer: Add a helper date-time field that equals the start plus one hour, and validate that the end is greater than it. In a step condition, use dateDiff(Start_Time, End_Time, "Hour", false()) > 1.

Why it happens

  • Field validations compare a field with a value or another field. They don't take a formula, so the one-hour offset needs its own field.
  • dateDiff skips weekends by default. Add false() so a gap across a weekend counts every hour.
  • dateDiff returns the absolute difference, so it doesn't tell you which field is later.

How to fix it

To block submission on the form:

  1. Add a Date & time field, for example Start_Plus_One_Hour, and make it computed with Start_Time.offset(1, "h").
  2. Hide it or set it read-only in permissions.
  3. On End_Time, add a Greater than validation and pick the Start_Plus_One_Hour field.
  4. Customize the error message, for example "End time must be more than 1 hour after start time."

To branch the workflow on the gap:

  1. Open the step or branch condition.
  2. Use dateDiff(Start_Time, End_Time, "Hour", false()) > 1.

You can also subtract the fields directly. Subtracting two date-time fields returns days as a number, so End_Time - Start_Time > 1/24 is true when the end is more than an hour after the start. See date expressions.

Related answers