API & Developers

API & Developers

Connect with other developers from around the world and collaborate on ideas using the Smartsheet API.

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

  • Community Champion
    edited 01/17/25

    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.

    Site faviconSmartsheet

    A sheet for an employee looks like this;

    Site faviconSmartsheet

    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.

    Site faviconSmartsheet

    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.

    Site faviconGroup data to organize results in report builder | Smartsheet Learning Center

    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. 

Trending in API & Developers