Office Status Calendar

My company has been researching on how to track the status of the employees working in office. We created a sheet where each day of the week, not including weekends, is a dropdown column with the choices of:
• IN OFFICE
• REMOTE
• OUT OF OFFICE
We also use conditional formatting for each column where if it selection is IN OFFICE then the cell turns green, but it is such a hassle to keep putting each of until the end of the year.
The question we have been trying to answer is there a better and more refined way of tracking it.
Attached is a excel of what it sort of looks like, due trying to keep names and other information out.
Can someone look it over and give me some advice?
Answers
-
Hi @ksandoval
Unfortunately, Smartsheet does not support conditional formatting via API as yet.
A solution I came up with is to create a sheet for each employee, vertically display the dates, add conditional formatting to the date column, and combine the sheets with a report, grouping them by date.
A sheet for an employee looks like this;
Formulas are;
WEEKDAY Formula
[WEEKDAY] =IF(WEEKDAY(Date@row) = 1, "SUN", IF(WEEKDAY(Date@row) = 2, "MON", IF(WEEKDAY(Date@row) = 3, "TUE", IF(WEEKDAY(Date@row) = 4, "WED", IF(WEEKDAY(Date@row) = 5, "THU", IF(WEEKDAY(Date@row) = 6, "FRI", IF(WEEKDAY(Date@row) = 7, "SAT")))))))
Calendar Formula
[Calendar] =RIGHT("0" + WEEKNUMBER(Date@row), 2) + "-" + WEEKDAY(Date@row) + CHAR(10) + WEEKDAY@row + CHAR(10) + IF(MONTH(Date@row) = 1, "JAN", IF(MONTH(Date@row) = 2, "FEB", IF(MONTH(Date@row) = 3, "MAR", IF(MONTH(Date@row) = 4, "APR", IF(MONTH(Date@row) = 5, "MAY", IF(MONTH(Date@row) = 6, "JUN", IF(MONTH(Date@row) = 7, "JUL", IF(MONTH(Date@row) = 8, "AUG", IF(MONTH(Date@row) = 9, "SEP", IF(MONTH(Date@row) = 10, "OCT", IF(MONTH(Date@row) = 11, "NOV", IF(MONTH(Date@row) = 12, "DEC", "")))))))))))) + CHAR(10) + RIGHT("0" + DAY(Date@row), 2)
"=RIGHT("0" + WEEKNUMBER(Date@row), 2) + "-" + WEEKDAY(Date@row) " part sorts the rows when the Calendar value groups the report.
You may want to add a filter to show part of the year, like Q1.
Limitation
The image below shows a report with 24 employee sheets.
As this article shows, the number of employees does not matter in a practical sense regarding the report's group capability. Still, the usability worsens as the number of employees on a report increases, inhibiting visibility over time.
To expand the groups when you load a report, make sure it has fewer than 2,500 rows that meet the report criteria. Smartsheet automatically collapses groups if your report has more than 2,500 rows.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives