Restricting a formula to a certain timeframe.

Eric Dittmer

Hi There,

I have a Smartsheet and Form that we use to calculate how long a person was checked into a function to account for down time. Since Smartsheet is not very good with calculating time it took some creativity go get a formula to match up 2 different rows with the same information to calculate the amount of time between them. Here is the formula

=INDEX(COLLECT([Out Time]:[Out Time], Date:Date, @cell = Date@row, Badge:Badge, @cell = Badge@row, Status:Status, @cell = "In"), 1)

The formula works, but a little too good and if the same person checks in and out multiple times in a day, it pulls the original check in time to calculate with the most recent end time. For example, if I check in at 08:05 and check out at 08:15, the formula totals 10 minutes. Now if I go back in the same day at 14:30 and check out at 14:35, it now shows 6 hours and 30 minutes instead of 5 minutes.

I tried adding an ABS function to restrict how far back the formula looks, but it did not return any results. I am not even sure if that is the correct function for this. The added formula looked like this

=INDEX(COLLECT([Out Time]:[Out Time], Date:Date, @cell = Date@row, Badge:Badge, @cell = Badge@row, Status:Status, @cell = "In", ABS([Out Time]@row - @cell) <= TIME(0, 90, 0)), 1)

Is there a way to restrict a formula to look back only 60 minutes so it does not keep adding together these multiple entries?

Lucas Rayala

Hi @Eric Dittmer ,

in the original formula you created, the collect function is actually getting ALL the values that match your criteria. In the INDEX function, the second value in your formula is 1. That’s saying “give me the first value provided by the COLLECT function”. However, you don’t want the first value, you want the last value. To get the last value, you could simply replace the 1 with a COUNT function that wraps the exact same COLLECT function. That will provide a count of the number of responses in the collect function. So if you have three responses in the COLLECT function, the count would return the number 3, and the INDEX would then look for the 3rd (or last) value in the COLLECT. Generically it would look like this:

=(INDEX(COLLECT(<criteria>)), COUNT(COLLECT(<criteria >)))

Lucas Rayala

Hi @Eric Dittmer ,

in the original formula you created, the collect function is actually getting ALL the values that match your criteria. In the INDEX function, the second value in your formula is 1. That’s saying “give me the first value provided by the COLLECT function”. However, you don’t want the first value, you want the last value. To get the last value, you could simply replace the 1 with a COUNT function that wraps the exact same COLLECT function. That will provide a count of the number of responses in the collect function. So if you have three responses in the COLLECT function, the count would return the number 3, and the INDEX would then look for the 3rd (or last) value in the COLLECT. Generically it would look like this:

=(INDEX(COLLECT(<criteria>)), COUNT(COLLECT(<criteria >)))

Eric Dittmer

Hi @Lucas Rayala,

Thank you very much for helping me on this!

Eric Dittmer

Hi @Lucas Rayala,

I fear I am not doing something correct adding the Count(Collect formula like you recommended. My original formula was

=INDEX(COLLECT([Out Time]:[Out Time], Date:Date, @cell = Date@row, Badge:Badge, @cell = Badge@row, Status:Status, @cell = "In"), 1)

and then adding Count(Collect with the return number 3, I got this.

=(INDEX(COLLECT([Out Time]:[Out Time], Date:Date, @cell = Date@row, Badge:Badge, @cell = Badge@row, Status:Status, @cell = "In")), COUNT(COLLECT([Out Time]:[Out Time], Date:Date, @cell = Date@row, Badge:Badge, @cell = Badge@row, Status:Status, @cell = "In")))

Unfortunately, the formula is coming back as #Unparseable and I do not understand why. Is it possible I am adding the criteria wrong for the second Collect function?

Thank you again for your help on this.

Lucas Rayala

@Eric Dittmer , you just have your syntax off. You closed your index function after the collect function by adding an extra parentheses.

Looks like I did the same thing in my example. Here’s the generic formula corrected:

=INDEX(COLLECT(formula), COUNT(COLLECT(formula)))

Eric Dittmer

@Lucas Rayala that makes perfect sense.

Thank you