Time calculations with data collected by a form

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).



Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    David Lane ✭✭
    Answer ✓

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!