Time calculations with data collected by a form

David Lane

I am using a form to collect data that includes a unique ID for each user. The user will check in by completing the form with their ID and "Arriving". When the user is done, the user completes the form again and selects "Leaving". I now have two rows of data where I extract the time in and time out from the creation date/time such as

How can I evaluate the ID and Status to find the values to calculate the total time. In other words, where ID matches a 'leaving' and 'arriving for each 'date' then calculate the total time spent in the room (leaving time - arriving time).

Thanks!

Paul Newcome

I would suggest a helper column where you will want to decide whether you want to pull the out time to the in row or the in time to the out row. If the second option, it would look something like this...

=INDEX(COLLECT(Time:Time, Date:Date, @cell = Date@row, ID:ID, @cell = ID@row, Status:Status, @cell = "Arriving"), 1)

From there you can run the time calculation built on solution found in the other thread.

David Lane

Thank you Paul! That is perfect and where I was stumbling.

Paul Newcome

There are a lot of time based solutions collected in the below thread.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

David Lane

Thank you for the collection of time calculations, but there is also the need to be able to correlate an ID with both an arriving time and a leaving time in order to complete a time calculation. The data collection will only be during a single day (no carry over of an arriving to a leaving on the next day) so that will slightly ease the problem.

To explain the issue differently....

User comes into room and completes an entry for ARRIVING. That same user then completes and entry for LEAVING. The two entries (two lines of sheet) need to be used to calculate the total time. For example ID 800123456 arrived and left twice during the same day. How can I use each set to calculate total time when there will likely be other lines of data between those two entries?

Paul Newcome

I would suggest a helper column where you will want to decide whether you want to pull the out time to the in row or the in time to the out row. If the second option, it would look something like this...

=INDEX(COLLECT(Time:Time, Date:Date, @cell = Date@row, ID:ID, @cell = ID@row, Status:Status, @cell = "Arriving"), 1)

From there you can run the time calculation built on solution found in the other thread.

David Lane

Thank you Paul! That is perfect and where I was stumbling.

Paul Newcome

Happy to help. 👍️