Practical uses for exported data from DeskTime

By consistently using DeskTime for automatic time tracking, you’re creating a vast database of accurate, insightful and invaluable information. DeskTime offers many options to export this information, but simple number don’t mean anything until they’re put into context. Here we’ll go over some practical uses of exported DeskTime data, and how to make it happen in your Excel spreadsheet.

The images of this tutorial were taken from a Mac computer using Open Office, the free software. Other excel versions may be a bit different, but do have the same functions available.

Calculations

1. Create a basic overview of all of your employees

We’ll show you how to get a basic overview table of all of your employees, which you’ll be able to manipulate based on what information is important for you. This information is accessed by the Owner of the account (the owner has access to all of the employees), and an Administrator can do the same, but only for the employees that the Admin has access to. In the end, you’ll see something like this:

Your view of all exported employees

Your view of all exported employees

You’ll be able to manipulate and sort elements such as groups, productive/unproductive/neutral time, total time, offline time, arrival time, departure time, and amount of time spend from arriving at work, and leaving (this is show in the spreadsheet as Diff.)

To achieve this table, go to your reports section. Select “all employees”, and choose the time frame you’d like to work within. We suggest a monthly view to get a good idea of average times.

Screen Shot 2013-06-18 at 2.50.37 PM

Then put your cursor on the “Export to XLS” button, and select “Employees”.

Screen Shot 2013-06-18 at 2.51.04 PM

The XLS file will automatically begin to download. Don’t worry if the download doesn’t start immediately, the program simply might take a few seconds to collect all of the data (it’s a lot to collect!).

Select the entire page (by clicking the corner cell). Then go to “Data”, and then select “Filter”.

Now you should see that each column has a little arrow next to it, which allows you to sort the information that you’ve collected through your productivity tracking. You can find out, for example, what your employee productivity level is by clicking on the arrow next to the “Productive” column and choosing to order in descending order.

What you'll see once you've put filters on

What you’ll see once you’ve put filters on

The same can be done with all other columns, and can be combined amongst themselves, for example, by selecting an employee group that interests you, and to see how long each one has been in the office (looking at the column marked “Diff”).

2. Calculate employee monthly wages based on hours worked

One of the benefits of using DeskTime as a productivity tracking software is having an accurate account of how many hours are actually spent working. For employers paying hourly wages, this can be particularly useful to make sure that your company is paying for the correct amount of work done.

Start from the previous table that we’ve already created. From here, we create a Pivot table. This is done by selecting the entire spreadsheet (by clicking on the corner tile as directed above), and going to “Data”, the selecting “Pivot Table” and “Ok”.

How to find the Pivot Table

How to find the Pivot Table

Now we’ll be creating a graph based on the data that interests us. Any data that was collected in the previous spreadsheet (time, date, name, productive, difference, etc.) can be used. With the help of the Pivot Table builder, we’ll drag and drop the values we want to be calculated into the correct spaces.

Drag and drop the values you want to be calculated

Drag and drop the values you want to be calculated

Drag and drop the word “Name” from the “Field name” section into the square that says “Row Fields”. You should see all of your employee names automatically create the Y axis of your table. Then do the same drag and drop motion and pull “Days” into the “Column labels” square. Now you should have a table that gives you the names of your employees on one side, and the days of the month on the top. To make life easier, choose “more” and check “Results to new sheet” to start in a clear work environment. Your table will look like this:

What you'll see in your pivot table, when you enter the values of date, name and

What you’ll see in your pivot table, when you enter the values of date, name and “diff”

You’ll see the time that each employee has worked. Copy and paste the two columns (employees and total worked time) into a new spreadsheet.

Employees and total worked time

Employees and total worked time

To be able to calculate your employees’ wages based on numbers of hours worked, you’ll have to convert the total time into decimal format. To do that, enter a new formula into the next column. This is the formula you should use:

=INT(A2)*24+HOUR(A2)+ROUND(MINUTE(A2)/60,2)

You’ll see it create a result in the next row:

Converting your time to decimal format

Converting your time to decimal format

Now pull down the first square to the bottom of the table to get the decimal format of the worked hours for all of the employees. Each of your employees may have different hourly rates. In the next column, then add the hourly rates for each employee.

*For the advanced excel users, you should know that you can import DeskTime data on work hours to your current HR spreadsheets by using the VLOOKUP function. Don’t know what this is? Leave a comment at the bottom if you’d like us to make another post explaining how to do that.

To reach the sum of the employee’s salary, simply click on the next column next to it, and enter the function =B1*C1. You’l end up with this:

Employee salaries based on worked hours and hourly rates

Employee salaries based on worked hours and hourly rates

 And voila! Now you have your employee’s salaries, based on the accurate amount of hours they’ve been at work for. 

3. Find other information that may interest you, for example, project fees

You can do lots more from the Pivot Table we’ve created, by comparing different elements. For example, in this pivot table, you can see how much of the time spend on projects was productive, unproductive, or total time spent on it.

See how much time is spent on projects

See how much time is spent on projects

With information on your projects, you can also use the same principle that was outlined above, to find out how much a clients owes you based on the amount of time your employees have used to work on a certain project.

Collecting automatic data can be benefit you simply be being available to be manipulated in whichever direction you need it. This is only one example of the kind of data you can draw from DeskTime, and it ultimately depends on what your needs are, and what the reason is that you’re using automatic time tracking software.

Any questions about using employee productivity software to pull out automatic data? Any suggestions about what you’d like to see next? Leave a comment in the comment box below, and we’ll be sure to answer!

One thought on “Practical uses for exported data from DeskTime

Leave a Reply

What do You think?