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

Register and calendar integration

sahilhq

Jan 5, 2020

Hi,

I need assistance in creating a calendar which will show the number of locations where material deliveries will happen (5 locations) over 24 hours - 365 days a year. The idea is to then create a dashboard with the form and calendar and share it with the senior members of my team so that they can book delivery slots by filling in the form based on the availabilities they see on the calendar. I have setup a draft dashboard. The form in the dashboard is looking good but the calendar not so good (see attached screenshot). The calendar needs to somehow show all the slots available (12am - 11pm) on a daily basis at all the 5 different locations. Is there a way we could achieve this?

I also have set up a sheet that's driving the calendar (see attached screenshot). I think we need to think of a clever formula in the sheet to achieve this. Please help!!

https://us.v-cdn.net/6031209/uploads/020/1B2JAEJB68UE.png

https://us.v-cdn.net/6031209/uploads/270/701RJ25C2AGW.png

.

---

View Post

Form / Calendar Integrati

sahilhq

Jun 27, 2019

Hi, 

I'm working on developing an induction booking process that allows our team to book site induction through Smartsheets. Firstly, I intend to have a Smartsheet induction register that will include columns such as induction date, who the inductor is, requester, role, company, email address, etc. Any one who would like to book a site induction will be first required to fill in a Smartsheet induction request form and the content of the form will be quite similar to the induction register. Once the form is filled in, all the information gathered from the form will automatically be populated in the site induction register. Finally, I will integrate the schedule induction register sheet with Smartsheet calendar so that calendar can display important information from the sheet such as the no. of available spaces, venue, etc. 

I have a couple of questions in order to make the steps described above to work:

1) Is it possible for the requester to be able to see the date that are available when they are the filling out the form? For example, dates that are not available are greyed out and are un-selectable and dates that are available are selectable and enter into the form when clicked. From another Smartsheet  community discussion, I understand that a possible workaround would be to publish the calendar and the link to the form so that they could open the calendar and then the form. However, it would be amazing if we could let the requester see the available/unavailable dates without looking at the calendar. Is this possible?

2) How do I show in the calendar the available spaces for site induction? For e.g. the maximum no. of people that could have a site induction in one day is 25 and the total number of requesters we have received for a particular day is 22. How do I show in my calendar that there are three available spaces only? 

3) Is it possible to have a different calendar Smartsheet rather than simply changing the view from grid to calendar? I would like a different calendar sheet that somehow manages to extract the key data from the Smartsheet induction register (no. of available slots, venue, etc.).

 

---

View Post

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.