15 Dec 2015

Statistics result 10-km run Carrera de las Empresas Madrid 2014

#48 Statistics result 10-km run Carrera de las Empresas Madrid 2014

Note 21-12-2016:
For stats (made with R and Power BI) of last edition of this race, see this post:

Yesterday, so Sun. 13 Dec. 2015, the 10K run "Carrera de las Empresas" was held here in Madrid.
In this 'business run', companies (teams of collegues) compete in 18 categories (dimensions: number of runners in team (2,3,4), gender team-members (men, women, mix) and distance (6, 10 km)). I also wanted to participate with my company, Ilunion (coparate group ONCE), like I did last year, but unfortunately this time I could´t because I was injured. For the 2014 edition of this run I made an Excel with the results but I never published it because of time problems, but I thought to finish it last sunday, so although I did´t participate, my mind was with the race..

In the 2014-edition there were in total about 9000 runners (about 300 teams) from about 800 companies, For more info about the run, see:



and for some photos:


and for some statistics:


In fig.1 you can see the result, and, as always, in the end of this post you can find the
 download-URL´s of the Excel(s).

fig.1 statistics result (finish times) 10K run

So my Excel only has statistics of the 10K run, which (netto/chip-) finish-times I got from this PDF:


As you can see in this PDF, for each runner his team is normally specified as:
Name company +´'-' + number or letter, e.g. my team was named Ílunion-26.
I wanted to create also statistics about the companies (number of runners per company), so first I transformed the data of this PDF to get the company of a runner, so for my row in this Excel the transformation was: team  Ílunion-26 -> company  Ílunion. Then I calculated in the Power Pivot datamodel, with a DAX-formula 'distinct count' the number of companies (687) derived from the field team (total: 1848), see fig.2.

fig.2: calculating data 'Company' (EquipoDef) from field 'Team' (Equipo).

And then I created a statistic 'total runners per company', see fig.3 for top 25 companies
 (with most runners). NB: I don´t know if 'TR' is a company-name or maybe a dummy-value.

fig.3: total runners per company

I wondered if there was a correlation between the number of runners of a company and the best 'total-times' of a team (of 2,3 or 4 members).  I used the category '10K - 2 (male) runners per team' to test this, see fig.4 for the result, which shows the correlation is weak (-0.15, so far for the max (negative) correlation of -1). And in the graph (scatter-plot) you can see that companies with about 30 runners or more always have a total-time (sum of time of runner 1 (best runner of company) and runner-2 (2nd best runner of company) lower then 5000 sec, which is not the case for smaller companies (in the 'bin' of companies with 2 to 10 runners, the slowest total-time is about 8000 sec.). Although in this category, the winner was a team of a company with only 2 runners, from New Balance. I guess they run on NB-shoes, so this run can be a nice way to get some free promotion...

fig.4: correlation between the number of runners of a company and the best 'total-times' of a team


#Mirror 1: Google Drive (zip files with 2 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


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


7 Dec 2015

Month-calendar for December 2015 in Excel

#47 Month-calendar for December 2015 in Excel

In this post the new month-calendar for December 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Event of this month is Carrera de las Empresas 2015 Madrid (13/12), a 10K run, a competition between (teams) of companies, so an event where work and sports meet like in this blog. For more info about this event , see:



                                          fig.1 Dec.2015 calendar



20 Nov 2015

SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

#46: SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

On 21-11-2015, so tomorrow, Saturday, I go to this event: SQL Saturday Madrid 2015, #SQLSatMadrid, where several Microsoft SQL Server and Business Intelligence (BI) MVP´s give some talks, for more information see:


and for the time-schedule:


As you can see, there is a total of 27 courses, in 7 time-blocks of 1 hour, in 4 rooms, and for each course the schedule shows this data:
  • Course name
  • MVP name
  • Level (Beginner, Intermediate, Advanced)
  • Theme (Big Data, BI, Cloud, DBA), indicated by cell-color
  • Language (most are in Spanish but when the course-name is in English , I guess the course is in English)
To ´warm up´ for the sessions about PowerBI, I converted this schedule in a pivot-table and graph, using Power Pivot and Power Query, see fig.1 for the end-result.

 fig.1 Pivot table Courses SQLSat.

To make this Excel, first I copied the Schedule (PDF) to Excel (export to Excel in the tool Nitro PDF) and did some cleaning (delete rows with non-schedule data and 'undo' of cell-merges and delete empty columns), see file 'SQLSaturday459_Madrid2015_Schedule_20151120_v2.xlsx' in Downloads below.
And then I made a 2nd Excel with the statistics, and with PowerQuery I loaded the schedule data from Excel-1, see file 'SQLSaturday459_Madrid2015_Schedule_Statistics' in Downloads and fig.2-3 for some of the steps in the Power Query formula language 'M'. An example of an 'M-formula':

= Table.AddColumn(#"Replaced Value3", "Level_1", each Text.Contains([Value], "Beginner"))

which looks in Excel-1 if in a cell there is the word 'Beginner' (level of course) and if so, sets the value in the Level-1 column to True (and if not to False), for more details of this formula, see:


fig.2: Power Query import

 fig.3 Power Query formula to Unpivot all columns (Room 1-4') except 1st column ('Time')

And after the Power Query import I did some other things in Excel-2 like adding a Time-block column and Course-Level-column, hiding come columns etc.) to facilitate creating the final pivot-table.
BTW: The Course-Level column I made manually because this information in Excel-1 is not part of the cell (string) value, but color-coded. But probably this could also be automated (so the conversion from cell-color to a (string-)value, see e.g.:


I keep it short now, tomorrow I have to get up early, the event starts at 08:30, and that for a Saturday..



14 Nov 2015

Statistics result 10-km run Corre por el niño 2015

#45 Statistics result 10-km run Corre por el niño 2015

UPDATE 8-12-2015: I added another Excel with a boxplot with the finishing-times per category (men/women). In par. Downloads below, I marked this Excel with 'V2'.

UPDATE 16-11-2015: I added another Excel with the ranking per category (men/women).

On Sun. 8 Nov. I participated in the 5th edition of the 10K run "Corre por el Niño 2015" (#CorrePorElNiño), this year with over 8000 participants (10km, 4km and 1 km for kids).  This run is organized by hospital Niño Jesús (in Madrid), to raise funds for investigation of severe diseases with childeren, like e.g. cancer. For more info about this road-race, see:





For  some of my photos of the race (and that of 2013), see:


In this run the bruto (gun) time and netto (chip) time are measured, and the bruto-time is the oficial time.

I also participated in this run, and, like last year, I made an Excel with the statistics of the result, see fig. 1 for the result. For more info about this Excel with the 2015-results was made,which is almost the same as the Excel with the 2014 result, see my 2014 blog-post about this 10K run:


And here are 2 others sites with statistics and all finish-times of #CarreraPopular #CorrePorElNiño Madrid 2015:



(this last site used the netto-time in their statistics, although the oficial finish-time is the bruto-time).

The data from this Excel comes (like last year) from this site:


I copied the data from their site to my Excel and added some check-columns (which are hidden), which showed some errors (red cells):
- in the man/women category field ("Sexo") their was one row with value "Masculina" (in stead of "Masculino")
- for some rows, the netto-time was higher then the netto-time
For the result of the 'import' of the time-date of the website into Excel, see fig.2.

This year there were no chips for the people who registered on the day of the run, so my name is not on the list (sheet 'Data'). My time was something between 47 and 48 min. (I made a short stop during the race to say hello to my sister-in-law and niece who were supporting the runners and I didn´t stop my stop-watch during this break), so as you can see in sheet 'Data', this time corresponds to about ranking 240 (of the 1642 10K runners) and percentile 17. That is in the 'All-category' (Men and Women). And to know my percentile in the Men-category, I copied the Excel and deleted in the Data-sheet all rows with finish-time of category Women (and X - Unknow). The array-formula to calculate the Frequency-colums used 'named ranges' so these were automatically (re-)calculated. The array-formula looks like this:


- R_Time2 = range on Time-Bruto column on Data-sheet
- R_Bin1 = range on Time-column on Stats-sheet

And I also made an Excel for the Women-category, and a final one with all Freq.values (so for categories All, Man, Women) with a graph which shows the Percentile-ranks for these 3 datasets, see fig.3, in which you can see e.g that after 55 min. about 50% of the men had finished, and about 20% of the women. And in the Excel for the Men-category I saw my percentile was about 18%.

And in fig.4 you can see a boxplot with the finishing-times per category (Men/Women). This is a stacked barchart based on the differences of the '5 number summary' (min, Q1, Q2 (median), Q3, max.). To create the boxplot, in menu Desgin (of chart) I selected 'Switch rows and columns' and replaced the part of the stached chart for the Min. and Max for the 'whiskers' (implemented by error-bars in chart) of the boxplot. For a more detailed explanation, see:


In the boxplot you can e.g. easily see that 75% of the men (Q3) finished before 50% of the women (Q2, median).

It´s a pity in this run they don´t have more categories using also 'dimension' age-group, e.g. Men-Junior (until 30), Men-Senior A (between 30 and 50), Men-Senior B(older then 50) etc., so you could compare your finishing-time better (with more or less equals (same gender and age-group).

And to conclude, I want to thank hospital Niño Jesús and all the volunteers for making this run a really nice experience and the isotonic drinks and bananas they gave us after the race almost make you forget  the suffering in the last part of the run with some 'nice' slopes...

fig.1: statistics result 10K run "Corre por el Niño 2015" 

fig.2: Imported Time-data

fig.3: Percentile-Rank per Category (Man/Women)

fig.4: Boxplot with finishing-times per Category (Man/Women)


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


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



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




4 Nov 2015

Month-calendar for November 2015 in Excel

#44 Month-calendar for November 2015 in Excel

In this post the new month-calendar for November 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is the Science Week, 'la Semana de la Ciencia', in Madrid (2/11-15/11), where universities etc. open their doors to the public, see:


Last year one of the activites of the Science Week was a guided walk in a garden with Bonsai-trees, for some photos I made, see:


And if your are interested in some science about data-visualization and the important role of Exel in this, as I have tried to show in several posts in this blog (with label: 'Data visualization'), see:


--fig.1: Nov. 2015 Calendar



1 Oct 2015

Month-calendar for October 2015 in Excel

#43 Month-calendar for October 2015 in Excel

In this post the new month-calendar for October 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is the Worldcup Rugby (until 31/10), see:


The quote of this month, comparing soccer and rugby, I found on this poster:


It will be interesting to see how the teams who play against New Zealand will respond to their intimidating Maori war-dance, the famous 'Haka' (which until now worked well for them: in the Worldcup the 'All Blacks' currently are leading in their pool).  So this Worldcup no 'Waka Waka', but the 'Haka', for some nice videos, see :




fig.1 month-calendar Oct.2015



1 Sep 2015

Month-calendar for September 2015 in Excel

#42 Month-calendar for September 2015 in Excel

Note 10/9/2015: I updated this post, with a new Excel which includes a 2015/16-schoolyear-calendar, see fig.2 and dowload-url V2 below.

In this post the new month-calendar for September 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is (again) "La Vuelta Ciclista a España - Cycling Tour of Spain 2015", which is held from 20 Aug. to 13 Sept.,  for more info, see:


The reason to repeat the theme (la Vuelta was also theme of month in Aug.) was to write about a Dutch surprise in this edition of La Vuelta, Tom Dumoulin, who won yesterday in a magnificant race the 9th stage (in Valencia), and is now also leading in the general ranking.  For a video of (part of) the race, see e.g.:


I hope to see Dumoulin finish here in Madrid 13 Sept in the red jersey ( the Spanish yellow jersey).

Fig.1: month-calendar

fig.2: schoolyear-calendar



V2 (zip with Excel and PDF-version):

17 Aug 2015

Dashboard for Messi´s worldrecord of 91 goals in one year with Excel + PowerView

#41 Dashboard for Messi´s worldrecord of 91 goals in one year with Excel + PowerView

Note 9/12/2016: 
See for a new version (with also new charts) made with Power BI, my new post: 

Next week starts the Spanish soccer League and I thought that it would be a nice moment to make a Excel PowerView dashboard for the best player of Barcelona (and the world and of all times), Lionel (Leo) Messi, based on this dashboard I once saw:




This dashboard was made with BI-tool Tableau, at the moment that Messi broke Gerd Mueller's 40-year record "most goals scored by a single player in a calendar year", on 9/12/2012, which was until then 85 goals, see:


For my Excel-dashboard I used the Tableau data-set and added 5 records for the goals Messi scored in the rest of Dec. 2012, so Messi´s world-record is 91 goals (made with Barcelona and Argentina ('Seleccion'). Actually I made 2 versions of the dashboard:

v1: report has summary-data (e.g. total goals per competition) and detail-data (match-details like date, opponent-team etc) (see fig.1b)

v2: report only has summary-data, no detail-data (see fig.1a)

As always, the download-URL´s for these Excel files can be found at the bottom of the blog-post.

            fig.1a PowerView report-v2 in PDF (exported from Excel) with Messi´s world record

           fig.1b PowerView report-v1 in PDF (exported from Excel) with Messi´s world record

To make this dashboard in Exel I used PowerView,  a (free) add-in for Excel 2013 Pro Plus, a Business Intelligence (BI)-tool for ad-hoc reporting. I copied the (Spanish) data-set with all Messi´s goals in 2012  from the Tableau-doc. to a worksheet  in Excel (and I added English comments to the Spanish column-headers), see fig.2

     fig.2: data-table with all Messi´s goals in 2012 (in pic. filtered for 1 opponent-team)

 Then I added this data to the datamodel in PowerPivot, another Excel add-in to perform data analysis using e.g. DAX-formulas like this:

Suma de Goles:=SUM([Goles])

, see fig.3 (formula-bar).

      fig.3: datamodel in PowerPivot, with DAX-formula

For sorting the goals by month ('Mes') in 'time-order' (so not in alphabetic-order), I added in the datamodel a column 'MesId' (1= Jan., 2= Feb etc), and used this DAX-formulas:

mes: =FORMAT([Fecha2];"MMM")

mesdID =MONTH([Fecha2])

see fig.4

        fig.4: datamodel with calculated field for month and month-ID, for sorting in report.

Once created the data-model in Power Pivot, I  opened Power View and created the elements in the report, which are (from top to bottom, left to right):

  1. pie-chart: goals per score-mode (left-foot, penalty etc)
  2. stacked bar-chart: goals per month and score-mode
  3. tile (filter): goals per team
  4. total goals, 'Suma de Goles', calculated field from the datamodel
  5. total matches, calculated field from the datamodel with DAX-formula: Total Matches:=DISTINCTCOUNT([Fecha]) (fecha = 'date'). NB: field´s value is # matches in which Messi scored = 46, so not all matches which Messi played in 2012 = 69.
  6. slicer (filter): competitions
  7. pivot-table, goals per match (with match-details like date, score-mode etc)
So in report-v1 you can see the 3 ways in which you can filter data in PowerView: in the Filter-View pane (to the right of the report-canvas), with a Slicer or with Tiles.

 as V1, except for bottom report, which contains a table in matrix-format and chart with goals per team and competition. NB: this matrix is like a pivot-table in Excel, see fig.5.

     fig.5: Pivot-table and chart : goals per team and competition

Creating the report-elements is just a matter of dragging and dropping the columns of the datamodel on the report-canvas and selecting the 'presentation-mode', e.g. Table or Chart (the matrix-table and chart in report-v2 are created in the same way (have the same fields), only the visualization-mode is different.
Note that the report-elements are connected: if you  use in report-v1 the Slicer to filter the competition 'Champions League' (CL), all report-elements only show the goal-totals of the CL-competition, see fig.6.

     fig.6: Slicer, Competition = 'Champions League' (CL)

Or if you filter in the chart with the goals per month = Dec., all report elements are filtered for Dec., see fig.7.

   fig.7: Filter in bar-chart Goals per month = Dec.

Or if you use the filter in the filter-pane and filter on an opponent team, e.g. "Rayo Vallecano", all report-elements only show goals Messi made against this team (Rayo), see fig.8.

     fig.8: Filter in filter-view pane, opponent-team = Rayo.

When I finished the report, I exported (published) it to a PDF-file (also included in downloads below).

So with this post I wanted to show you the reporting-possibilities of Excel and PowerView. For another example, see my post about the Sochi Winter Games:


Interesting Links
On my research for this post I found some interesting links, including videos with the 91 goals from Messi, from which you should see at least one of the first 2. Or if you don´t have so much time, watch only the 3d video, with maybe Messi´s best goal ever, which he made when he was only 19.

Videos Messi:

Lionel Messi - All 91 Goals in 2012 with Commentary

Los 91 Goles De Leo Messi En 2012

Lionel Messi Goal vs Getafe

https://youtu.be/8o_Lionel Messi - EL D10S - Short Movie |HD

Lionel Messi - The Magician - 2015 ● Skills ,Goals ,Dribbles , Assists |HD

Infographics Messi´s 91 goals:

*1 positions field from where Messi scored

Messi vs Mueller


 Statistics career Messi with Barcelona


Business Intelligence with Excel and Powerview

Getting Started With Power View In Excel 2013 

Creating KPI dashboard using Excel 2013 PowerPivot

From Data to Insight & Impact: Building a Sales Summary with Power View

Excel 2013 Power BI Tools Part 17 - Filters in Power View


#Mirror 1: Scribd.com (PDF file report-V2):


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


#Mirror 3: Google Drive (1 zip file with Excel and PDF files report V1 and V2):

1 Aug 2015

Month-calendar for August 2015 in Excel

#40 Month-calendar for August 2015 in Excel

In this post the new month-calendar for August 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is "La Vuelta Ciclista a España - Tour of Spain 2015", which starts 20 August, for more info, see:


This cycling Grand Tour also passed through the mountains Picos de Europa (Asturia), a very nice part of Spain where I spent several holidays, for some photos, see:

https://www.flickr.com/photos/46731978@N00/7573362742/in/photolist-c: eqYW-cxereq-cxerEL-cxestW-cxesTG-cxes2m-cxetdw-cxetNb-cxeu37-cxeuvE/

In my calendar I used a clipart from:

-- fig.1

Downloads (Excel and PDF):


2 Jul 2015

Month-calendar for July 2015 in Excel

#39 Month-calendar for July 2015 in Excel

In this post the new month-calendar for July 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is "(Free) Summer Courses by Open University Madrid (UDIMA)", aprox. 70 courses 'online' (with Google Hangout) during the whole month of July. The courses are about various themes, one of them is Business Administration, with a course about Excel: "Treatment of Accounting data with MS Excel. Pivot-tables and Power Pivot".  So the courses are free, online (type: Open Education), last about 1 hour, are in Spanish and had in 2014 over 10.000 'students' of about 56 countries (mainly Spanish-speaking). For more info about the UDIMA summer courses, see:





25 Jun 2015

Vacations planner for 2015 in Excel

#38 Vacations planner for 2015 in Excel

Note: I made a similiar Excel for 2014 (see post #20), but the 2015-version has some new features, like date-formulas, to calculate e.g. the weekday (in fig.1 these have the Spanish names, like Lu(nes) = Monday).

The Excel in this post is a vacations planner for 2015. It has all dates of 2015 and for every resource (team-member) you (team-manager) should mark with a number for the 'vacations request status' which date(s) he wants to take his vacation and if that is possible or not, like this:
 1: applied (yellow)
 2: aproved (green)
 3: refused (red)

In fig.1 you can see a small example of a company with 2 employers (John and Jane) which have to agree about their vacation-dates, in a way that there is always at least 1 person at the office. The last column 'Conflict' = 1 (red) if both Jane and John have a vacations-request (with status 1) for the same day.

And to conclude this post:
If you don´t have any plans yet for these summer holidays, maybe I have an idea for you, see sheet-1 of the Excel for a photo of my holidays-destination in 2011, Islas Cies, playa Rodas (Galicia - Espana),  which was elected that year by the readers of the newspaper 20minutos as the nicest beach of Spain, and this newspaper used one of my photos to show why, see:


 fig.1: Holidays-planner



2 Jun 2015

Month-calendar for June 2015 in Excel

#37 Month-calendar for June 2015 in Excel

In this post the new month-calendar for June 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner.
Theme of this month is "Rotary Streetsoccer Oldenzaal 2015", a soccer-tournament for schools in Oldenzaal (Netherlands), with 338 teams (2200 kids) participating this year, a new record.  For more info about this tournament, see:


and for some videos, see:

And for photos, see:

As I wrote in a previous post, see:

I made a computer-program, Match, for this tournament (6 years ago), with MS Access. For some
of the reports of Match for this year´s edition of the tournament, see:




And for all other Match-reports and everything else about Match and how it can be used for competitions like Rotary Streetsoccer, see the user manual:

Note that for this calendar I used '#' to color-format the days of the tournament, and that the weekdays are in Dutch, which I configured in sheet-1, where you can select from varios languages.

And to conclude this post I want to recommend you this Excel.course by TU Delft (Delft University of Technology, where I studied computer science):


Data Analysis: Take it to the MAX(), a course with videos about how you can do data-analysis with MS Excel.




4 May 2015

Month-calendar for May 2015 in Excel

#36 Month-calendar for May 2015 in Excel

In this post the new month-calendar for May 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner.
Theme of this month is "La carrera de la mujer Madrid", a 10K race for women to raise funds for breastcancer research, hold on Sun. 10/5 with over 30.000 participants. For more info, see:





6 Apr 2015

Timesheet for worked hours 2015 v3 with total hours per project

#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:


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:


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:


(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:

(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)
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)
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


  fig.5: SQL query to join data of 2 employee-timesheets

For more info about this unpivot-solution, see:


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. :


And for more info about MS Query, see e.g. :


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:
which contains over 15.000 (free) programs (which includes Excel-files like my Timesheet), and not all programs are in Spanish.


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


#Mirror 2: Google Drive


2 Apr 2015

Month-calendar for April 2015 in Excel

#34 Month-calendar for April 2015 in Excel

In this post the new month-calendar for April 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner.
Theme of this month is Semana Santa (Easter Week) in Madrid, with processions in the street in which you can see men with cone-shaped hats (see fig.1). For more info, see:

and for a video o Semana Santa in Sevilla, maybe the most famous one of Spain, see:




4 Mar 2015

Month-calendar for March 2015 in Excel

#33 Month-calendar for March 2015 in Excel

In this post the new month-calendar for March 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner.
Theme of this month is Spring, which in Madrid, with days of 20C already, is anounced by the almond-trees in park Quinta de Molina as you can see on my photos:


fig.1 Month-calendar March 2015



11 Feb 2015

Excel Statistics for 20Minutos Blog Awards 2014

#32 Excel Statistics for 20Minutos Blog Awards 2014

NB: Si prefieres leer este blog en español, recuerda que con Google-Blogger (el herramiento usado para este blog) es posible traducir este blog de ingles a español. El 'Google Traductor' esta en el menu a la derecha.

The Spanish newspaper 20Minutos, the 2nd newspaper in Spain, with a daily (free) edition of approx. 1 million, organizes the 9th edition of its Blog Awards ("Premios 20Blogs"), the biggest blog-contest in the Spanish speaking world. The 2014 edition has 7223 participating blogs from (mostly) Spanish speaking bloggers, and they are not only from Spain but e.g. also from Latin America (where BTW 20Minutos is also published in 2 countries: USA and Mexico). Until 2nd of March everybody with a 20Minutos account can vote 20 times, one time for every blog-category, and the blog with the most votes wins the public prize, although the jury decides who will be the winner of the 1st prize: 5000 euro.
My blog exists over 1 year now, and although it´s not in Spanish, I also signed-up my blog, and 20Minutos accepted it. Maybe they accepted that my blog is in English because English is so popular in Spain nowadays, there are so many people doing courses, even MP Rajoy seems to have done the course 'learn the 1000 most used English words'. Or maybe my blog was accepted because I have several post which have something to do with Spain, or Madrid where I live, e.g.:

* 2015 year-calendar with holidays in Spain and Madrid ('calendario laboral'), with Spanish names of weekdays and months, see:

* timesheet to track worked hours (´plantilla de control de horas trabajado', 'los partes'), with integrated Spanish calendar, and e.g Spanish summer-schedule (´horario intensivo'), see:

* Excel with statistics results 10K run 'Corre por el niño' (organized by children hospital Niño Jesus), see:

* Infographic win/loss record of Rafa Nadal, see:

* Match schedule 2014 FIBA Worldcup Basketball Spain (´Mundial baloncesto') , see:

20Minutos allows you to promote your own blog to attract votes in the Blog Awards, so: if you like my blog, you can vote for it here:


To do so, you have to register here:


But enough self-promotion, let´s start with what this blog is about: MS Excel. And I thought it could be interesting for all the participants of the Blog Awards to see some statistics about it. Of course 20Minutos also made one, about the number of participating blogs per year, see:


And this is 1(of 4) statistics in my Excel, see fig.1.
NB: in their graphic, the total of 2013 is not correct (6790), or better said, not corresponding to their data on this page (my source):           

This graph shows that the number of blogs participating in the Blog Awards increased almost every year, from a bit more than 2000 in 2006 to over 7000 in 2014.



The chart in fig.2 shows for the 2014 Blog Awards the number of blogs for all 20 blog-categories (except 'Best Blog' and 'Public Prize', because of course all blogs participate in these 2 'categories'). My blog is in category 'Blogosfera' (a 'Rest'-category, so for blogs which don´t fit in any other category) with 933 blogs, although I thought I signed up for category 'Internet etc.', with 502 blogs, so with 'less' competition then in category 'Blogosfera', but still, 501 competitors... Maybe next year I´ll participate with a blog about motors .. (this category has only 48 blogs).



The charts in fig.3 and 4 are similar, both have the 'dimensions' (axis) time and blog-category, but they differ on which axis (x or y) these dimensions are placed. Both have a filter: Rank blog-category <= 3, so it shows per year only the top 3 blog-categories (excluding the 'Rest-category'). In fig.3 you can see that there are 5 categories which every year have the most blogs, and that in the beginning the 'Personal-category' was the most popular but this decreased during the years. And in fig.4 you can e.g. see that before 2011 the 'Personal-category' was the one with ranking 1, but that since then, the 'Culture-category' took over.

How did I make this Excel? I used this page as my data-source:

Maybe I could have used the 'copy/paste' method to get the data from this (tabbed) HTML-table in Excel, but I thought it would be nicer to use the Power BI option of Excel, and use the function "Get external data from website", but this didn´t work. But from this site:


I learned that Google-Sheets (Google´s Excel..) is better in 'scraping' data from websites then Excel. So I created a spreadsheet with Google-Sheets on Google-Drive (so in the cloud) and this function did the job perfectly:

=IMPORTHTML("http://lablogoteca.20minutos.es/premios-20blogs/ediciones-anteriores/2013/", "table",1)

I shared this Google-spreadsheet here:


see also fig.5 

fig.5:  Google-spreadsheet with data imported from website 20Minutos

And then I exported (downloaded) this cloud-spreadsheet from Google-Sheets to my 'good-old' desktop Excel (version 2013). And this Excel is the database (´server´) for the Excel with the statistics (´client´), and to 'bind' them, I used an Excel-query, and to get the data of every year, with 1 worksheet (and 'named range') per year in 1 table, I used SQL´s union-operator:

SELECT R_2006.Categorias, R_2006.Ganador, R_2006.Concursantes, R_2006.Rank, R_2006.Año
FROM `D:\projects\20Minutos_BlogCompetition2014_v4_input.xlsx`.R_2006 R_2006
SELECT R_2007.Categorias, R_2007.Ganador, R_2007.Concursantes, R_2007.Rank, R_2007.Año
FROM `D:\projects\20Minutos_BlogCompetition2014_v4_input.xlsx`.R_2007 R_2007


For more info about using Excel as a database, see my post:

The 4 statistics in my Excel are made with pivot-tables, about which I wrote in several posts, e.g.:

And to conclude this post, I´d like to say good luck to all my fellow-bloggers in the "Premios 20Blogs" Blog Awards.


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

#Mirror 2: MS Onedrive (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


-client (with statistics):

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

1 Feb 2015

Month-calendar for February 2015 in Excel

#31 Month-calendar for January 2015 in Excel

In this post the new month-calendar for February 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner.
The event of this month is ISU World Cup Speed Skating, February 7-8, 2015, in 'ice-temple' Thialf ( Heerenveen, Netherlands), see for more info:


fig.1 2015-Calendar month Feb.


#Mirror 1 :


13 Jan 2015

Timesheet for worked hours 2015

#30 Timesheet for worked hours 2015

Note (8-4-2015)
I made a new version of this Excel, which can calculate total project-hours, see:
This post is about the new version (V2) of the Excel I made  to track your worked hours, the "Timesheet 2015". For my post about the previous version (V1), see:


What´s new in V2:

- V2 has the calendar for 2015 and the holidays in Spain/Madrid.
- V2 is better documented, and uses cell-comments to describe the columns of the 'input-table' (where you have to enter your worked hours)
- V1 showed all columns with formulas of the input-table, V2 only shows 1 calculated column, that with the end-result of all calculations (difference between your worked hours and contract-hours, which should be 0). This to avoid the risk that you accidentily change a column with a formula.
- V1 was mainly in Spanish and had a translation-table for English, V2 is in English
- V1 had, besides de the timesheet, also a year-calendar and vacations-planner, V2 doesn´t, these documents are now in separate Excels, see:



- V2 has more statistics.
- V2 has an extra input-table, to book the time you worked for a project per day per office-hour
- V2 has a new office-hours/work-week table for a short workweek of 35 hours.
- V1 was free of charge and V2 isn´t.. No, I was just kidding, V2 is also for free, but  if you want to make a small donation, I appreciate it. My PayPal-account is related to my hotmail-account "eigersoftware" (see also sheet-1 of the Excel of this post).

How to use this Excel:

step 0: delete all my input for this demo (green columns in sheet 'Tabla'), which has data for every (working) day in 2015 (with extra details for month decembre, that´s why I filtered the input-table for this month)

step 1: the Excel has some 'parameters' which you should check and maybe change, like e.g. the table with the office-hours, e.g.: for a 40 hour work-week. NB: you can use more then 1 workweek-table per year, e.g. in Spain during the months July and August, we have a summer-schedule of 37 hours in stead of the standard 40 hours-workweeks in the rest of the year.

step 2: fill for every day in sheet 'Tabla' (see fig.2)  the green columns of the input-table, like start-time and end-time of working-day and break(s). The yelow cell  'Tdiff' shows the difference of time-worked vs time-contract (should be >= 0), in minutes. And you can also register the time you worked for a project per day per office-hour, see sheet 'DiaHoras'.

step 3: update pivot-tables in sheet 'Estadisticas' (see fig.1), click menu Analizar > Actualizar Todo (o menu Datos > Actualizar (Analyze > Refresh). For more info about pivot-tables, see:

For more details about this Excel, see the document itself, the demo-data, especially that of december should give a good impression how to use this Excel to track your hours.

And to conclude I want to thank Excel-MVP John Walkenback for adding my blog on his website about Excel, which is a great source of information about Excel, see:



Fig.1: Statistics: pivot-tables and graphs to show total worked time (and difference with time-contract, so the time you should work)

Fig.2: Input-table with start-time and end-time per day (input) and total hours worked and difference with contract (calculated). Table is input for pivot-tables (see fig.1).

Fig.3: Input-table with time you worked for a project per day per office-hour


#Mirror 1:

 #Mirror 2 (Excel file):

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


#Mirror 3: