Creating an editable weekly schedule on Sharepoint

Question

For the new hybrid way of working (present in the office versus working remotely) I need to create a schedule on Sharepoint where colleagues fill in their availability for the coming week. I have made an Excel spreadsheet with all colleagues and the Monday to Sunday (formula =NOW(), =NOW()+1 etc.), week number based on one of these dates and count of "IN" occurrences to establish how many colleagues are present in the office in any given day. I tried creating a List but I found the formulas can only be applied on Column level, while I only have 2 rows of formulas: 1 the header with the weekdays and 1 with the totals. How can I best reproduce this schedule on Sharepoint, in a more web friendly way than just importing the spreadsheet?

Answer 1

Hi @Ankutzu
SharePoint list formula could not work on rows (in SharePoint called items), every item is added manually.

So as a workaround, we need to add all the colleagues' names to column, to those who are on work the title we set the value to "1", to those who are on rest we set "0", then add a calculated column which uses formula to add up all the values so that we could get the number of how many colleagues are present in the office.:

The item we need to added one by one manually, here we set the title (Monday to Sunday) by ourselves, and the whole list is like the following:

What's more, you could create an excel file first ,then create a list from excel, which you could refer to:
https://learn.microsoft.com/en-us/answers/questions/514294/34unexpected-token-a-in-json-at-position-034-when.html

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Yi Lu_MSFT , thank you so much for your kind support.

The solution you propose, with creating separate columns for each colleague, will not work as it's not user friendly enough for our organization of 80+ people (one should scroll all the way to the right to find their column).

As a starting point I created a simple Sharepoint list that only requires manual input. It works well for now however I can't figure out why conditional formatting (I've selected color highlights depending on the input) only works for some cells and not for others. Looks like it's not being updated, as it works when I drag the formatting from one cell to a similar one, but it reverts after a while to the standard formatting.

In the attached image you can see the empty cells, which are supposed to be yellow, are showing the standard highlight and not the one assigned.