Office Status Calendar

ksandoval

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.

OFFICE CALENDAR STATUS.xlsx

Can someone look it over and give me some advice?

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=ef66a49742f14c3597ea154f6cdc2a57

A sheet for an employee looks like this;

https://app.smartsheet.com/b/publish?EQBCT=08d6906475f34cf7a33c9559c3e48ba8

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.

https://app.smartsheet.com/b/publish?EQBCT=1fb97b6d25f84a1482f60b297652f23c

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.

https://help.smartsheet.com/articles/2482082-configure-grouping-to-organize-results-in-report-builder#toc-tips-and-tricks

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.