10 Jan 2016

Timesheet for worked hours 2016 and PowerView report with stats

#50 Timesheet for worked hours 2016 and PowerView report with stats

 This post is about the new version (V3) of the Excel I made  to track your worked hours, the "Timesheet 2016". For my post about the previous version (V2), see:

http://worktimesheet2014.blogspot.com.es/2015/04/timesheet-for-worked-hours-2015-v3-with.html

Timesheet-V3 does not have any new features comparing with V2, the only difference is that V3 has the 2016-calendar (V2 was for 2015). So in this post I won´t repeat the explanation about the timesheet, for this see the post of Timesheet-V2 (2015).

But there is one thing new for the 2016-Timesheet: I made a new Excel with a PowerView report with statistics about worked days and holidays at year-, month- and week-level. The Excel with the Timesheet also has a worksheet with statistics, but these are using the 'traditional' pivot-tables, while the new Excel with PowerView uses PowerPivot Tables which has some new nice features which I´ll show below.

To show how this Timesheet works, I made a demo in which I registered check-in and check-out times for all (working-)days in 2016, and also the days off (vacations, sick, personal days).
If you want to use this Excel to track your worked hours, you should delete all my input for this demo (green columns in sheet-2: 'Tabla').

In fig.1 you can see the Excel with the PowerView report.
NB: 
- this report shows Month-stat. for Dec. and Week-stat. for week 50 (which starts on Monday (Spanish: Lunes) 22 (Dec.) (see filter-pane, to the right of report-canvas)
- the report-titles are generated, and have some Spanish:
'por' = by
 'y' = and

And in fig.2 you can see the Excel with the Timesheet-data (fig.2) and -statistics (fig.3).

In the rest of this post, the figures are from the Excel with the PowerView report.



fig.1 Excel with Powerview-report for Timesheet




fig.2 : Excel with Timesheet (table)



fig.3 : Excel with Timesheet statistics


In fig.4 you can see that if you filter in the bar-chart Hours Worked per Month for 1 month , e.g. Dec., then also the other report-element at year-level, the pie-chart Days Off, is filtered for this month.


fig.4: filtering in report (month = Dec.)


In fig.5 you can see the result after I clicked in fig.1 in pie-chart Days Off on the blue-part of the pie-chart, labeled Code2: 'VA' (= vacations) and in fig.6-7 you can see how to drill-down in this pie-chart to Code3: VA0 and VA1 (VA0: vacations-days of last year, VA1: vacations-days of actual year).


fig.5: Pie-chart Days Off (not worked)





fig.6: Pie-chart Days Off , config. drill-down Code2-> Code3 (see field: Color)


fig.7: Pie-chart Days Off , result drill-down Code2-> Code3


To get the data from the Timesheet-Excel (worksheet-2, data-table 'Tabla2')  into the PowerView Report-Excel I used PowerQuery (see fig. 8) and I imported the data in the PowerPivot datamodel. After that I added some DAX-formulas in this datamodel, e.g. to calculate the worked time in hours, with decimals (in the Timesheet-Excel, the time is registered in minutes):

HoursWorked := ROUND([Ttotal3]/60;2)

and to get some more 'friendly names' for fields and field-values in the pivot-table, and in English rather than Spanish, I used e.g. this formula:

IsWorkingDay:=IF([EsLaborable]=1;"Y";"N")

And in the PowerPivot datamodel I also renamed some fields to more 'friendly names' and marked some fields as 'not visible in client', so that the final-user (you) only has the fields which are usefull in pivot tables (e.g. WorkedHours, but not 'Notes').



fig.8: PowerQuery to import Timesheet data  

For the resulting PowerPivot datamodel, see fig.9-10.


fig.9: PowerPivot datamodel with DAX-formula

--
fig.10: PowerPivot datamodel with friendly field-names and hidden fields


As you can see, the new Timesheet statistics, with PowerView, are better then the old ones. Some people call Excel with the PowerBI-plugins (PowerPivot, PowerView, PowerQuery and PowerMap) 'Power Excel', and say 'Power Excel' is the best tool for all kinds of data-analysis, which results in an interesting discussion, see:

http://www.powerpivotpro.com/2015/06/no-more-apologies-excel-is-the-worlds-best-data-tool-period/


Some references: 

* For a (free) course about data-analysis with MS Excel, by my old university, TU Delft (Delft University of Technoloy), see:

https://www.edx.org/xseries/data-analysis-business

* For a video with more details about how to create a timesheet with Excel, see:

https://youtu.be/vYdHi2m4IgE

*For a good video and blog about MS Excel and Powerview (and more Microsoft software), see:

http://www.wiseowl.co.uk/blog/s389/power-view.htm

https://youtu.be/8PUmuud7TYg



Downloads:

NB: The Excel with the Timesheet-data, which is input for the Excel with the PowerView-report, should be in this dir:

C:\tmp\zzz\TimeSheetWorkedHours_2016_DEMO_R1.xlsx


#Mirror 1: Google Drive (zip file with 2 Excel files):

zip-file with 2 Excel files:
https://goo.gl/OQFI0j

PDF:
https://goo.gl/23LLQs


#Mirror 2: Microsoft Onedrive (1 Excel file: Timesheet):

NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC

http://1drv.ms/1mO95vj


#Mirror 3: Scribd.com (1 PDF file: PowerView report):

https://es.scribd.com/doc/295022256/Excel-TimeSheet-WorkedHours-2016-with-PowerPivot-report-for-Stats











2 Jan 2016

2016 Year calendar and planner in Excel

#49 2016 Year calendar and planner in Excel

In this post the new year calendar and planner for 2016 and the month-calendar for January, see fig.1-3. For the Spanish readers: the calendar includes all holidays in Madrid-capital, so it´s the 'calendario laboral 2016', see:

https://sede.madrid.es/portal/site/tramites/menuitem.1f3361415fda829be152e15284f1a5a0/?vgnextoid=4366addba8200510VgnVCM1000000b205a0aRCRD&vgnextchannel=239be59bdb789210VgnVCM100000171f5a0aRCRD

or this site, which also has a year-calender (in PDF):

http://www.calendarioslaborales.com/calendario-laboral-madrid-2016.htm

My calendar also has the (ISO) week-numbers (week 1 is the week with the 1st Thursday of the year). And it is multi-language (English, Spanish, French, German, Dutch, Italian, Korean, Chinese).
NB: For more about the Korean and Chinese variant, see earlier 'Calendar-posts' (see my blog-posts with tag/label 'Calendar' at the end of this post).


The graphic I used in this Excel, this time about the new year 2016, which is the year of the monkey in the Chinese horoscope, is from this site :

http://thumbs.dreamstime.com/z/chinese-new-year-monkey-year-symbol-calendar-red-figures-illustration-53158214.jpg

And for more info about the Monkey- (leap) year 2016, see:

http://www.guy-sports.com/humor/christmas/chinese_new_year2016.htm

Happy 2016!



fig.1 Month Calendar January 2016



fig.2: Year Calendar 2016



Downloads:

#Mirror 1: Google Drive (zip file with  Excel and PDF files):




#Mirror 2: Microsoft Onedrive (1 Excel file):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC

http://1drv.ms/1MMB2sr


#Mirror 3: Scribd.com (1 PDF file):

https://es.scribd.com/doc/294441979/2016-Year-Calendar-and-Planner