Many companies are very well utilizing Office 365 for project collaboration. They, however, still have spent much effort for building solution without considering using out-of-the-box features. Building timesheet management that helps them accurately track and pay hours for whether internal employees or freelancers is one of the most common cases that give a headache. To maximize utilizing Office 365 for project management, this article guides you on how to build an effective timesheet management on Office 365 without having to write code.
Create Timesheet Content Type and Site Column
To facilitate and help manage timesheet effectively, creating timesheet site content type is always a recommended best practice. It’s because site content type is managed in a centralized location and it can be flexibly reused across sites in Office 365.
Using site column is also recommended. With this, you don’t have to recreate settings each time you want to reuse for another lists, document libraries or sites. It provides a consistency for information management as well.
First, define your new content type as follows (Open Site Settings > Site Content Types > Create)
After creating the timesheet content type, you need to create site columns for this content type. Define site columns as described as follows (open Site Settings > Site Columns > Create)
All of these columns are stored in a new group named Timesheet Columns. Each setting of each column depends on your specific requirement. For example, in the column Hours, the min value should be 0 and the max value must be less/equal to 24. It’s because obviously a day can’t contain over 24 hours. The value at the setting “Number of decimal places” can be 2 or 0 if you want to round up number.
With the site column Timesheet Month and Timesheet Year, you can use the following formula for each.
- Timesheet Month: =TEXT([Date], “MMMM”) (Date is the site column you’ve created)
- Timesheet Year: =TEXT([Date], “YYYY”) (Date is the site column you’ve created)
Note: Do not copy the syntax from my blog. Type it by your hands manually
After creating site columns, you need to add it to the timesheet site content type and set status for them (Hidden, Required, Optional). For example, if only you are to manage and see how much your team spent, value in the column Resource should be hidden.
Open Site Settings > Site Content types > Timesheet. In the Site Content Type page, click Add from existing site columns and then add all columns you have created for the timesheet content type.
Below is the structure of the timesheet site content type you have completely created.
Add timesheet content type to a custom list and create view
After you create the timesheet site content type and add all necessary site columns to it, you need to create a new custom list and add the timesheet content type to this list.
Open List Settings of the new timesheet list named Timesheet Report and click Advanced settings (under General Settings). Set Yes under Allow management of content types?
Another note, in the Item-level Permission setting, check Read items that were created by the user and Create items and edit items that were created by the user. This configuration is to prevent your employee from seeing effort of another.
To make timesheet more visual, you need to create custom view or modify the existing view for timesheet list. In the ribbon, click Modify View. Check the columns you want it to be visible to your employees.
In the Sort setting, under First sort by the column, select Timesheet Year column and Show items in descending order. You then may need to sort by month, just select Timesheet Month under Then sort by the column, and don’t remember selecting Show items in descending order.
If you want to group items by both month and year, you need to create a new calculation column and set the following formula:
In the Group By setting, under First group by the column select Timesheet Year-Month. Select Project Code column if you want to group by this column after Timesheet Year-Month.
The last thing you may like see is the sum of all hours your employees spent. In the Totals setting, select Sum at the Hours column.
Finally, you can export the list to Excel in order to report your client if necessary.
This article gives you a very quick solution to build an effective timesheet management. You can even build an automatic calculator for billing against the timesheet using Office 365 out-of-the-box feature.
Update 30/07/2017: a few people reported to me that this article has some issues with the new modern UI in SharePoint Online. Acknowledged this fact, modern UI experience is not mature yet, and people are still preferring the classic experience. I’d say this article is applicable to classic experience only.