Looking to track cleanings

Dave Schwager

Currently I'm looking to add a validation mechanism to a sheet and would like to have a systems that reports after (X number of) cleanings have been completed for the day. This would add a row displaying the room that was complete for the day. If the room was not complete it would show up in red as not complete.

I'd also like the time the cleaning log was reported to be a separate row not attached to the date like the modified date shows. Currently it shows up as [ 09/29/23 8:21 AM ] I'd like it to show up in two different columns.

Thanks for the assistance.

-Dave Schwager

Andrée Starå

Hi @Dave Schwager

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Dave Schwager

Hi @Andrée Starå

Thanks for responding so quickly. So as you can see below in my screen shot. The time shows up with the date and not separate. I'd like it separate or a way to validate that cleaning cycles were at least 2 hours apart.

I also need 3 cleanings a day logged. So when we get three cleanings. I'd like something to capture that the room was completed for that day. I have an error column. If anything but N/A is put in the comments. It flags the error column for that submission in red to let the supervisor know that comments were made or something was missed on the inspection / cleaning list.

Thank you,

-Dave Schwager

Andrée Starå

@Dave Schwager

Happy to help!

Interesting! I'll have an idea, and I'll get back to you!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Dave Schwager

@Andrée Starå Awesome! Thank you! I tried using the # Auto number feature, but sadly it has no option for a end number. Only a starting number. I was able to get to this in the Column settings.

Unfortunately it keeps counting past 3 and doesn't go back to 1. I'd like the forms to send all the data at the end of the day to a historian file. My plan is to make a report that shows rooms complete that have no errors and were cleaned X number of times successfully. Then put that information into a dashboard. Each day all the information collected on all the forms will move to a historian record for long-term record keeping.

Hope this helps. I think it's a awesome project. I can reset the auto counter manually, but that would be a pain in the butt to do it every day on all the forms. Even when the rows are moved. The counter picks up at where it left off and has to be manually turned off / on and saved to reset it.

Paul Newcome

You can use a DATEONLY function in a date type column and a RIGHT function in a text/number column to split the date/time stamp.

You can use various solutions found in the below linked thread to get the time output as a usable number and then use an INDEX/COLLECT or INDEX/MATCH to pull the time from the previous entry for that room, compare it to the time "@row", and if the difference is less than 2, flag it as too frequent.

=IF([Time As Number]@row - IFERROR(INDEX([Time As Number]:[Time As Number], MATCH(MAX(COLLECT([Auto-Number]:[Auto-Number], [Auto-Number]:[Auto-Number], @cell< [Auto-Number]@row, [Room #]:[Room #], @cell = [Room #]@row)), [Auto-Number]:[Auto-Number], 0)), -2)< 2, 1)

And you can use a COUNTIFS in say a checkbox column to count how many times the room number is found within the same date with no "too frequent" flags thrown then drop that into an IF statement to say that IF that COUNTIFS is greater than or equal to 3, check the box for "complete for this date".

And finally you can use Conditional Formatting to highlight the cells in the Comments column that do not equal "N/A".

https://help.smartsheet.com/function/dateonly

https://help.smartsheet.com/function/right

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