A small nonprofit organization needed a better  process for time tracking and reporting. Our solution is to let users enter their time data into a SharePoint Online list by way of an InfoPath form. And we use Excel to display the data in a dashboard.

The organization has two paid employees and the rest are volunteers; the board also consists of volunteers. The paid staff registered their hours in an Excel file. The employees work irregularly and they get more money for work that they perform at night or in the weekend:

 

00.00 – 07.00

07.00 – 22.00

22.00 – 24.00

Monday – Friday

30%

 

20%

Saturday

30%

 

20%

Sunday and holidays

100%

50%

100%

 

Each month, the Excel file was mailed to an external agency for salary processing. The payroll administrator then had to  calculate, based on the Excel file,  how much the employees should be paid for the irregular hours in addition to the fixed salary. The requirement is to calculate this automatically, because the external agency bills the organization for all of this manual compilation and calculation and that is getting too expensive. The solution I have made uses the following table for premium pay.

The Excel files gave very little insight into the hours spent by the two employees. The board wanted more control over the work performed by understanding the hours spent.

The current Excel files gave the employees insight how many hours they worked and how much leave they have available. This functionality should remain available.

The solution

The organization is already using Office 365 for non-profits, which is provided free by Microsoft through TechSoup. TechSoup is an organization that governs software donations for non-profit organizations.

Because the organization is currently already using Office 365 and SharePoint Online, I chose to enter the hours in SharePoint. An InfoPath form ensures a correct input and validation. The reporting is done in Excel and displayed on a dashboard in SharePoint with the Excel Rest API.

Below I will describe the steps I followed to create the solution.

Step 1: List for time registration

The core of the solution is a list in SharePoint. I’ve created a subsite below the existing teamsite for that list. This is needed to give the external agency only access to the solution. At first I had created a list in the existing teamsite, but then the account for the agency could not refresh the data in Excel. The account needs read permissions on site level to refresh data and we don’t want the external agency to see the rest of our teamsite.

I called the subsite ‘Timesheet’ and on the subsite I created a list called ‘Time Entry’. To prevent the two employees from editing each other’s entries, I changed the item-level permissions. You can reproduce this step as follows:

Go to the list setting, select Advanced settings and at Item-level permissions select ‘Create items and edit items that were created by the user’.

Create the following columns.

On the following columns I set these settings:

–        Title: default value ‘Time entry’

–        Year: default value ‘2015’ (update each year)

–        Week: minimum and maximum allowed value between 1 and 53

–        Day: choice column with every day of the week and holiday

–        Time Period: choice column with the choices from the table above (00:00-07:00, 07:00-22:00, 22:00-24:00)

–        Work Type: choice column with different work types as choices

–        Task: choice column with different tasks as choices

Step 2: InfoPath form for entry

Next, I’ve edited the ‘New item’ form. We use InfoPath to allow the employees to enter their data quickly and easily. We are aware of the fact that InfoPath will be phased out, but we can continue to use it for years anyway.

I didn’t use the option in the Ribbon to edit the form, but I started InfoPath and by using the wizard we will get the right settings. The reason is that InfoPath then gives you more options, like the option to include repeating tables. We want the employees to be able to enter their hours for the entire day or even the entire week in one go.

You can reproduce this step as follows:

Start InfoPath Designer and chose SharePoint List.

Fill the URL of the SharePoint site.

Chose for the list you just created.

At Advanced Option  select ‘Manage multiple list items with this form’, allowing the employees to enter all of their hours for the entire day or the entire week in one form. Click the Finish button.

On the loaded form, you see all the fields displayed vertically. Remove Title, Employee and Year. Move the other fields next to each other, horizontally, and it should look like the image below. Probably you want to add a description above the form to explain how it works. Every separate item is a separate row in the form and will become a separate item in the list: different tasks, or the same tasks in different time periods etc.

Next, we need to set a couple of rules to prevent wrong data being filled in by the employees.

First of all, we want the field Employee automatically filled by the current user’s name. Right-click on Employee under Fields and select ‘Field Properties’.

In Value fill in this formula: substring(userName(); 19; 255)

Secondly, we want the irregular hours premium pay (called ORT) to be filled automatically. To accomplish that, the columns ‘Day’ and ‘Time Period’ has to be filled first, before we calculate the ORT percentage.

So we create a rule that the Time Period only can be filled if the Day also is filled.

In the ribbon click on ‘Manage Rules’ and select the column Time Period.

Select New and then Formatting.

Fill in a title and in the Condition field you select ‘Day is equals to blank’. If this condition is true, then the column must be disabled. Check ‘Disable this control’.

Then we create a rule that will fill the ORT field with the correct value. Choose New and then Action.

I will pick one rule as example: On Sunday night the employee will get 100% ORT. The rule will have this condition:

If Day is equal to Sunday and Time Period is equal to 00.00-07.00 and the task is not Vacation/leave then the ORT field must set to 100.

There will be another 10 rules for calculating the ORT. I added the full table with rules below.

We need to add vacation/leave to the condition. The employees will fill in the form how many hours holiday they have used, but never can get ORT (premium pay) over this hours. To prevent this we create an extra rule on the Day column.

Select the column Day, choose New and then Formatting.

Fill a title and as condition select Task is equal to blank. If this condition is true then the column should be disabled, check the ‘Disable this control’ option.

Finally we create a rule that will empty the columns Time Period and Day when Task or Day are changed. If we do not do that, it would still be possible to get premium pay on vacation/leave.

Below I add the rule on the Day column, but you also must do it on the Task column.

Choose for New and then Action.

Don’t fill any condition, so that the action will always be executed on any change of this field. As action we set the fields value must be equal to “”, we just don’t fill anything in here.

To summarize, we now have the following rules on the following columns:

Rule on column

Name rule

Condition

Action

Day

Empty Time Period

None

Set a field’s value: Time Period = (empty)

Task

Empty Day

None

Set a field’s value: Day = (empty)

Time Period

Set ORT 100 Sunday night

Day = Sunday and

Time Period = 00.00-07.00 and

Task != Leave

Set a field’s value: ORT = 100

Time Period

Set ORT 30 night

Day != Sunday and

Day != Holiday and

Time Period = 00.00-07.00 and

Task != Leave

Set a field’s value: ORT = 30

Time Period

Set ORT 20 evening

Day != Sunday and

Day != Holiday and

Time Period = 22.00-00.00 and

Task != Leave

Set a field’s value: ORT = 20

Time Period

Set ORT 100 Sunday evening

Day = Sunday and

Time Period = 22.00-00.00 and

Task != Leave

Set a field’s value: ORT = 100

Time Period

Set ORT 100 Holiday night

Day = Holiday and

Time Period = 00.00-07.00 and

Task != Leave

Set a field’s value: ORT = 100

Time Period

Set ORT 100 Holiday evening

Day = Holiday and

Time Period = 22.00-00.00 and

Task != Leave

Set a field’s value: ORT = 100

Time Period

Set ORT 50 Sunday

Day = Sunday and

Time Period = 07.00-22.00 and

Task != Leave

Set a field’s value: ORT = 50

Time Period

Set ORT 50 Holiday

Day = Holiday and

Time Period = 07.00-22.00 and

Task != Leave

Set a field’s value: ORT = 50

Time Period

Set ORT 0 day

Day != Sunday and

Day != Holiday and

Time Period = 07.00-22.00

Set a field’s value: ORT = 0

Time Period

Set ORT 0 leave

Task = Leave

Set a field’s value: ORT = 0

 

Step 3: use Excel for reporting and dashboard

For reporting, we will use Excel. Go to the Time entry list and click in the ribbon on ‘Export to Excel’.

By creating a couple of PivotTables, we give insight in some details about the time entries. Firstly, we create a table with ORT information for the payroll administrator. We share the Excel file with the administrator so he/she has access to the latest premium pay information.

I created a couple of tables and charts to give some insight in the hours spent. I saved the file to a Document Library on SharePoint.

To make these insights easy accessible for the board, I’ve created a dashboard on a page in SharePoint. By using the Excel Rest API, you can show the tables and charts on the page as images or html. You can do that as follows:

You can access an overview of all available tables and charts using the API. Enter the following url in Internet Explorer, substituting the names of your tenant, site, etc into the address:

https://[tenant].sharepoint.com/sites/[SiteCollection/[Subsite]/_vti_bin/ExcelRest.aspx/[Documentlibrary]/[Excelfile].xlsx/Model

You will see the screen below.

When I click on Charts, I see all available charts. I created a chart for the hours an employee spent in a week on a task. When I click on the name of the chart I will get to see an image. I copy the url in the address bar and on the SharePoint page I choose adding a new picture by url and paste the url.

The url looks like this:

https://[tenant].sharepoint.com/sites/[SiteCollection/[Subsite]/_vti_bin/ExcelRest.aspx/[Documentlibrary]/[Excelfile].xlsx/Model/Charts(‘ChartHoursEmployeeTasks’)?$format=image

When I choose  a table and click on the link I will get a 404 error. I get this url:

https://[tenant].sharepoint.com/sites/[SiteCollection/[Subsite]/_vti_bin/ExcelRest.aspx/[Documentlibrary]/[Excelfile].xlsx/Model/Tables(‘TableHoursBalance’)?$format=atom

This is a known issue in the API. You can work around this issue by changing the last bold text to ‘image’ or ‘html’. Then you will see the table as an image or in html.

Below you will see a part of the dashboard we use.

Conclusion

We have built a timesheet that employees use to register hours by entering them in an InfoPath form that saves the data to a list in SharePoint Online. Rules in the form guide the user and calculate the premium pay (ORT) they are entitled to. The payroll administrator automatically gets an overview of the premium pay the employees should get, in addition to their regular salary. And the board can see what the employees have done for that money, in a dashboard based on Excel charts and tables displayed on a SharePoint page by way of the Excel Rest API.

This solution has been used for nine months now, and the users are very happy with it: it is easy to use, less error prone and far less time-consuming than the old way of working.

(25333)

Views: 25.33K  | Categories: Forms, SharePoint
About The Author
  • gerrybrim says:

    Thanks, Frank! Great stuff. I have developed very similar solutions for a few clients. I learned some things here and liked the way you did it.

    One thing worth mentioning would be the item limitations in the list. If they are using list views (which it appears you are avoiding – hooray!) they can only view 5,000 items at a time. The solution that I’ve used for this was to provide a procedure to archive old timesheet records. Even without the List View limitations you may still want to do this for performance and to avoid old records being edited accidentally or maliciously.

    The other thing I added was a simple “processed?” yes/no flag field that the account uses: this defaults to “no” and when she runs invoices and tracks vacations she sets it to “yes” (using quick edit view). This becomes handy for building views and for the archive function mentioned above, and avoids double-billing or other gotchas.

    Thanks again!
    Gerry

  • DavidRemillard says:

    Excellent article!  Really shows how you can leverage native capabilities from different parts of O365 to create a total solution that adds business value.  Nice!

  • FrankOptLandt says:

    Thanks for your comments 🙂

    Gerry, great feedback. I will consider archiving and the processed column

  • breezy16 says:

    Though I have to try for SP Online, on separate cases, I used Excel’s Data Connection and Excel Web Services to display the SP List data using Excel Web Part in SP2013. This way, the user with restricted access to Excel Pivot Tables can control the data display via Filters. With your solution, I learned something, too! Thanks!