Employee In-Office Tracking - How to Set-up

jtl

I'm trying to develop a Smartsheet to allow employees to self-report when they will be in the office each week with the goal of other people seeing who plans to be in so they can collaborate/have lunch. The idea is that it would send an automation each week to the person for the upcoming week where they can indicate if they are remote or in-office (and what building they will be in). However, I want a report to only show the current week to make it easy to view. I have about 100 employees I need to do this for. Does anyone have any genius thoughts on how to set this up?

Nik Fuentes

@jtl

first and foremost, I would avoid giving columns specific date names wherever possible. there's only a maximum of 200 columns in one document, so by your current meathod you'd not have enough room for a whole year's data. What's more, forms think in columns, but can't adjust automatically; so by this system you'd have to manually adjust the form to the next week's columns every week.

I think that a good solution here would be to switch to a set of generic columns like Monday, Tuesday, etc. You could then have two columns per day, one for the date and one for the status. the date columns give you hard numbers that you could then filter by [e.g. if [Monday Date] is 7 days from today]. See this example. The 5 Date columns are, well, date columns, the Status columns are dropdowns, the Week# is a formula using the Weeknumber() function, and the name is the primary.

You could easily add a bunch of bells and whistles to this like a status column, conditional formatting to grey out past dates, automatic dates so the user doesn't have to enter more than one date, etc. But I think that this could work well enough as is.

Nik Fuentes

I'd start by setting up a sheet with a form that collects the basic information you'd be asking for. You can then have the automation run out of that sheet and have the link to the form in the body of the alert. Employees fill out the form and it dumps into the top/bottom of the sheet. Then you have a report that looks at that sheet and filters to only look at dates in the next/last 7 days [etc].

Alternatively, you could have a column formula on the sheet that collects the form responses that checks if the date(s) provided are this week and flags them somehow if they are [checked checkbox if it's this week]. The report would then just be filtered for checked rows. It might take a little futzing to get the 'if this week' formula working, but it would keep the report easy and clean.

Either way, the automation with a form and a separate report is probably your best bet.

jtl

@Nik Fuentes - thanks for your comment. I'm not sure how you operationalize what you say in your first idea. The goal is that they would receive a form with the dates so I would set-up my sheet like the below and then they would fill in the WFH/building information. However, if i do it this way, I can't filter to look at the next/last 7 days. And the below would require me to have column headings for each date which I ideally don't know want to do either. I like where you are going, just not sure if I understand how you would set it up.

Nik Fuentes

@jtl

first and foremost, I would avoid giving columns specific date names wherever possible. there's only a maximum of 200 columns in one document, so by your current meathod you'd not have enough room for a whole year's data. What's more, forms think in columns, but can't adjust automatically; so by this system you'd have to manually adjust the form to the next week's columns every week.

I think that a good solution here would be to switch to a set of generic columns like Monday, Tuesday, etc. You could then have two columns per day, one for the date and one for the status. the date columns give you hard numbers that you could then filter by [e.g. if [Monday Date] is 7 days from today]. See this example. The 5 Date columns are, well, date columns, the Status columns are dropdowns, the Week# is a formula using the Weeknumber() function, and the name is the primary.

You could easily add a bunch of bells and whistles to this like a status column, conditional formatting to grey out past dates, automatic dates so the user doesn't have to enter more than one date, etc. But I think that this could work well enough as is.

Andrée Starå

Hi @jtl @Nik Fuentes

I hope you're well and safe!

The limit is 400 columns, so you could fit one year if needed.

I hope that helps!

Be safe and have a fantastic week!

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!

Nik Fuentes

@jtl Okay, I stand corrected on the max column number, but my other point about the forms still stands.

Also, a whole year's worth of columns sounds awful to set up.

jtl

@Nik Fuentes - no worries; we are all allowed to be wrong once in a while. :) If I did a whole year's worth of columns I would build in excel in import in, that way you don't need to do columns one by one.

With that said, I like your solution, I'm working on building to make sure I can easily operationalize. I appreciate your help.

Andrée Starå

@Nik Fuentes

What I usually do in my client solutions is use an Excel sheet or similar and then Import it and either use that as a starting point or move one row to the Smartsheet Sheet I'm working on and have all the columns added.

I use this method once and then save the template structure in Smartsheet.

jtl

@Nik Fuentes - just wanted to say thanks again. I created something a little different in that I just put a week start/end instead of individual columns for the dates. This is going to work great. Thanks so much!!!!