#35 Timesheet for worked hours 2015 - v3, with total hours per project
Note 10-1-2016: I made a new version of this Timesheet Excel with statistics in a PowerView-report, for 2016, see:
http://worktimesheet2014.blogspot.com.es/2016/01/timesheet-for-worked-hours-2016-and.html
This post is about the new version (V3) of the Excel I made to track your worked hours, the "
Timesheet 2015". For my post about the previous version (V2), see:
http://worktimesheet2014.blogspot.com.es/2015/01/timesheet-for-worked-hours-2015.html
What´s new in V3:
*1: worksheet-4 ('DiasHoras'), to track your worked hours per day+hour per project now also has the total hours per project
*2: I created a new Excel that can consolidate the Excel-Timesheets of several employees in 1 Excel, usefull for a Human Resource (HR) department which wants to see e.g. the total hours worked on a project by all employees working on that project, see fig.1.
fig.1: consolidated Timesheet
The reason to create this new Excel was not for personal use (for my
work I only track the 'punch in- and out times'), but a question of a
company of Chile who read my blog about the timesheet I created.
The asked me:
"We need a Timesheet that shows how many hours an employee has worked in a project X, considering that there are many ongoing projects and 10 employees in the company who may be working on several projects on the same day. And also: what is the total of hours worked in project X, so for all employees working on project X. "
They also sent me an example of how they want to track the worked hours per employee and project, in crosstab table/Gantt-chart format, see fig.2 (every project has a unique color).
fig.2: Timesheet example of reader of my blog with question
As you can see in my blog-post:
http://worktimesheet2014.blogspot.com.es/2015/01/timesheet-for-worked-hours-2015.html
(in fig.3), this example looks very much like what I had with my Timesheet-v2, except that I used timeslots of 1 hour (in stead of 0.5 hour as in the example of fig.2), and that my timesheet is for use of 1 person (in stead of the 3 persons as in the example of fig.2), which I solved by creating a new Excel which consolidates all employee timesheets.
Now more details about the new features of Timesheet-v3.
To calculate the total of hours an employee worked on a project X I used this formula:
=SUMIF(Tabla5[@8]:Tabla5[@20];$V$6)
(In Spanish Excel that is: CONTAR.SI)
See fig. 3 for an example of the total project-hours for employee Bruce: the input-table, name 'Tabla5', has in columns H to T (columns 8 to 20) the (color-coded) projects on which Bruce worked (Analysis - green, Design - blue, Coding - red, Deploy - yellow), and the total project-hours are in the table 'Projects' (columns V to AA).
NB: this table repeats the date-column (A) of table 'Tabla5' in column AA, but this column (AA) could better have been the 1st column (V) of table 'Projects' (so that the project-columns can expand to the right without limits).
This example shows e.g. that Bruce worked a in december 23 hours on project 'Analysis' and in total 144 hours (so on all projects).
fig.3: total project-hours for employee Bruce
And fig.4 shows another example of an (individual) Excel timesheet, of employee Colin, which has of course the same format as that of employee Bruce (fig.3), which is necessary to be able to consolidate the 2 Excels into 1. The only difference (in format) is that of the Projects-table, which does not have the same columns: Colin didn´t work on the Deploy-project (as Bruce did), but on the Document-project (for the rest of the projects, Colin and Bruce worked together). In this example Colin worked on the same amount of projects as Bruce (4), but this is not a necessary condition for consolidation of the 2 Excels.
fig.4: total project-hours for employee Colin
To consolidate the 2 Excel timesheets of Bruce and Colin in 1 Excel, I created a new record-structure:
(Employee, Project, Hours, Date) (see fig.1) and to 'transform' the data of the 2 Excels of employees Bruce and Colin, which have record-structure (Date, Hours Project-1, Hours Project-2, .., Hours Project-N, see fig.3-4), I used a kind of 'UNPIVOT' solution (UNPIVOT is a Transact SQL command (MS SQL Server) to create 1 row for each 'pivot-column', which in this case is the column 'Project'), which SQL-code looks like this (see fig.5 for full code)
SELECT 'Bruce' AS [Employee], 'Analysis' AS [Project], R_Hours_EmployeeB.Analysis AS [Hours], R_Hours_EmployeeB.Date
FROM `C:\Temp\TimeSheetWorkedHours_2015_DEMO_v2_B.xlsx`.R_Hours_EmployeeB R_Hours_EmployeeB
WHERE (R_Hours_EmployeeB.Analysis>0)
UNION ALL
SELECT 'Bruce' AS [Employee], 'Design' AS [Project], R_Hours_EmployeeB.Design AS [Hours], R_Hours_EmployeeB.Date
..
SELECT 'Colin' AS [Employee], 'Analysis' AS [Project], R_Hours_EmployeeC.Analysis AS [Hours], R_Hours_EmployeeC.Date
FROM `C:\Temp\TimeSheetWorkedHours_2015_DEMO_v2_C.xlsx`.R_Hours_EmployeeC R_Hours_EmployeeC
WHERE (R_Hours_EmployeeC.Analysis>0)
UNION ALL
SELECT 'Colin' AS [Employee], 'Design' AS [Project], R_Hours_EmployeeC.Design AS [Hours], R_Hours_EmployeeC.Date
FROM `C:\Temp\TimeSheetWorkedHours_2015_DEMO_v2_C.xlsx`.R_Hours_EmployeeC R_Hours_EmployeeC
etc.
fig.5: SQL query to join data of 2 employee-timesheets
For more info about this unpivot-solution, see:
http://stackoverflow.com/questions/18536639/how-to-unpivot-a-crosstab-like-table
This query also uses a UNION procedure to join data ('named ranges' which you can query as tables using MS Query) from several Excel-files, which I explained in previous posts, see e.g. :
http://worktimesheet2014.blogspot.com.es/2015/02/excel-statistics-for-20minutos-blog.html
And for more info about MS Query, see e.g. :
http://exceluser.com/formulas/msquery-excel-relational-data.htm
In my example I had a company with 2 employees and a total of 5 projects, but the solution (1 Excel template 'Timesheet' for employees, 1 Excel 'Consolidated Timesheet' for Human Resource (HR) department and 1 query to import the data form the Employee-Timesheets in the HR-Timesheet) is generic, that is: you can apply it for a company with any number of employees and projects. In the query you have to add a 'union-block' for every employee, and for every employee 1 'union-block' for each project he did.
NB: Creating this query can be some work, especially if you have a lot of employees and/or projects and there is a risk you make an error, e.g. forget to list a project for an employee. Therefor it´s important to cross-check the total projets hours of the consolidated timesheet (fig.1) with those from the individual timesheets (fig.3-4). An alternative would be to use a normal table instead of a crosstab table to input the worked hours (making the 'Unpivot' construction in the query unnecessary), but this would mean your input-table would have at least 240 rows (30 (#days per month) * 8 (# working-hours per day) and 1 project per day), and you lose your Gantt-chart representation.
For the final result (a pivot-table and pivot-chart for the consolidated timesheet-data), see fig.1, which shows e.g. that Bruce and Colin worked both 144 hours in Dec. and that the Coding-project was the one which consumed the most of their time.
BTW: how did the company from Chile find my blog? Probably from this (Spanish) site:
http://www.portalprogramas.com/
which contains over 15.000 (free) programs (which includes Excel-files like my Timesheet), and not all programs are in Spanish.
Downloads
NB:
The 'input- Excels' C:\Temp\TimeSheetWorkedHours_2015_DEMO_v2_B (with data from employee Bruce) and C:\Temp\TimeSheetWorkedHours_2015_DEMO_v2_C (with data from employee Colin) have to be in dir. C:\Temp so that they can be imported in TimeSheetWorkedHours_2015_DEMO_v2_BC (consolidated timesheet, so with data from employee Bruce and Colin)
#Mirror 1: MS Onedrive
NB: this site has 'Excel-Online', so you can view my Excel-files here if you don´t have MS Excel on your PC
http://1drv.ms/1ceJiYj
#Mirror 2: Google Drive
http://goo.gl/AYYNmw
