Need a solution for tracking employees in the office

Andrea Zenner

Hello,

I have been tasked with creating a "calendar" in which our employees can sign up to be physically in the office so we can monitor numbers. Right now we can only have 25% of our employees in the office at any one time. I can do this in SharePoint but we are currently using on-prem so people need to be connected to VPN and there is no mobile option like we would have with SS.

Requirements:

1. People need to be able to sign up for a date or range of dates. If someone is in for a full week we don't want them to have to fill out 5 forms.
2. No more than X number of people can be in the office at any one time. This number is 12 currently but will increase as COVID rules relax.
3. Once X number of people have signed up for a particular date we want to prevent people from picking that date (or give them a visual that shows a particular date is "full")
4. Ideally we would like a calendar view that shows available slots for any particular day. We don't want to see 12 or 25 names on a calendar, that will get messy but we would like some sort of visual on a calendar for capacity for any given day.

Has anyone already cracked this nut?

Paul Newcome

There have been a few solutions very similar to this here in the Community that I have seen. Try doing searches on open slots in calendar and calendars on dashboards. I will also dig around and see if I can find something for you here in the Community as well since I seem to have misplaced my notes on how to actually build it out.

Paul Newcome

Here are two examples that should help you get started on the build...

https://community.smartsheet.com/discussion/65042/register-and-calendar-integration

https://community.smartsheet.com/discussion/51736/form-calendar-integrati

Andrea Zenner

@Paul Newcome Thanks much for the responses. @Debbie Sawyer assisted me in creating the application in a far simpler manner than those posts. I'm all set now. Thanks again.

Paul Newcome

Are you able to post screenshots and/or a walk-through of the setup so that others searching for a similar solution can avoid the apparently over complicated version(s)?

Ali Holder

@Andrea Zenner Would love to know how you did this! Looking for about the same set up!

Andrea Zenner

@Ali Holder I'd be happy to get on a WebEx and show you the solution.

I have a "sign up" sheet. At the top of the sheet I have all the information regarding dates and spaces available.

I have one cell in the first row in which I put the total number of available spaces for the office.

In this sheet there is a column for date, prepopulated for 90 days. Each row has the formula Today(1), Today(2), Today(3), up to Today(90). That way I don't have to manage the available dates, they automatically roll.

I have a "count per date" column. The formula here is =COUNTIF(Date:Date, Date@row) - 1. This counts the number of times a particular date is used in the lower half of the sheet (where people sign up) minus the one entry in the top of the sheet.

The next column determines if there are spaces available based on the count of spaces used. The formula is =IF([Count per Date]@row >= [Full or Spaces]$1, "Full", "Spaces Available").

In our calendar that our users view to see if there are spaces available, we show how many are available for that day using this formula. =IF([Full or Spaces]@row <> "Full", ([Full or Spaces]$1 - [Count per Date]@row) + " - Spaces Available", "Full")

We also color code our calendar (we use the Calendar App) so I have a column for color code based on whether there are spaces available or not. =IF([Full or Spaces]@row = "Spaces Available", "Available", "Full")

Then on the bottom section of the form, the user sign ups are stored. Users sign up using a form. They enter their email address and the date they will be in the office.

Since they just enter their email address and they want to see names on one of the calendars, I calculate their name from the email address in the first column with this formula: =UPPER(LEFT(Alert@row)) + " " + UPPER(MID(Alert@row, 2, 1) + MID(Alert@row, 3, FIND("@", Alert@row) - 3))

I don't remember now why the email address had to be a text field but it is, so I copy it to an Alert column which is a contact list type so alerts can be sent to users if they sign up for a date that is full with =[Email Address]@row

The date column is populated from the form the users fill out.

The count per date column counts the number of times that date has been used in the bottom of the form. To make this work, the first row, highlighted DO NOT DELETE is my placeholder row so I can count from that row down. So the formula in the Count per Date column is =COUNTIF(Date$122:Date@row, Date@row)

I have an approved/rejected column so when a date fills up, should someone sign up for that date, they get an alert that they signed up for a full date and they can't come in the office. I also get that alert and I delete their entry from the sheet. The formula is =IF([Count per Date]@row > [Full or Spaces]$1, "Request NOT Successful", "Approved")

I also have an "archive" column that is checked by default when the form is submitted. This allows me to have an automation that moves any dates in the past to an archive sheet so we are only dealing with today forward in our main sheet.

I have three workflows. One that archives past dates so any date that is in the past, where the archive flag is set (don't want to archive the top of my sheet!) are moved to an archive sheet.

Another workflow sends a confirmation to the users if they want one. On the form they fill out they check a box if they want confirmation of their request. If that's checked the user gets a copy of the form they filled out.

The third is the Day Full message if someone signs up for a date that is already full.

For the most part people like it.

What they don't like:

They have to sign up for each day individually. I couldn't figure out a way to have them enter a range of dates and have all the formulas work. I was under a pretty tight deadline so I just didn't have time to figure that part out. For people who are in the office every day, I just go into the sheet and add them. That's usually our executive staff.

What I don't like:

If people are not going to be in the office after they have signed up to be we have to delete that row so the space becomes available for others and so our tracking of who said they were in the office is correct. I didn't want to give users access to delete items (and, again, I didn't have time to figure out a way to have a flag that would delete a specific row....) so they fill out another form which sends an alert to me and I delete the requested date. It's really not a big deal, it doesn't happen often and only takes seconds to do as I'm in SS almost all day anyway.

This is long. I hope it helps. Again I'd be happy to show you the full application. We have it on a dashboard now but I wish I had started working in WorkApps before I built it. It would be much more friendly mobile if I had done that.

Ali Holder

@Andrea Zenner Wow!! Thank you for this! I would love to hop on a webex if you have some time to walk me through it. I would also really like to see your calendar set up. Not sure if they have private messaging, but please let me know what is the best way to give you my email to set up a time to chat.

Thank you!

Andrea Zenner

@Ali Holder Email me at azenner@harmoninc.com and we can set up a time.