Formula help - difference between two form submissions based on a couple of

Cathy Fraser

Hello, I need assistance with creating a formula to calculate the time difference between two submitted forms. One form is submitted when a person checks in, and a second form is submitted when they check out. I'm seeking a formula that considers the date stamps in relation to the Site Address and Check-in Action columns. I attempted using an AI tool, but it wasn't able to provide the help I needed, possibly due to a lack of clarity in my request. 😊)

Chris Mondeau

Hi Cathy,

Maybe this will help a little bit.

I created 2 columns (key and difference):

The Key column just merges values from your record into a unique identifier used to find group all rows together that match the Company, Name, and Address:

The idea behind having this is that there may be a scenario where you have 2+ contacts going out to the same site and checking in/out separately.

In the Difference Column, I'm calculating the difference between the minimum and maximum time created for the record pair (based on the Key column created previously). This formula only accounts for time, but here's a reference link where you can see additional adds for days and times as well:

Key Formula: =[Company Name]@row + Name@row + [Site Address]@row

Difference Formula: =(TIME(RIGHT(MIN(COLLECT(Created:Created, Key:Key, Key@row)), 8)) - TIME(RIGHT(MAX(COLLECT(Created:Created, Key:Key, Key@row)), 8))) * 24

A quirk for the time formula, when applied to a system date/time column you need to look at the 8 rightmost characters to parse out the time values. In my formula you also see a multiple of 24 which converts the value to a decimal hour.

Hopefully that helps!