Data collection is not for free. While an operator is recording data, he/she is not producing. In general variable data such as a speed, temperature, etc. requires some sort of equipment to measure it and therefore it is more expensive to collect than attribute data: Pass/Fail, On-time/Late, etc.

Time is a key variable in any process which can automatically be collected by any PC, Portable, Tablet or Smartphone, therefore it does not require any additional measuring equipment.

Entering a date or a time manually into a terminal is prone to multiple typing errors which if undetected can distort all subsequent key metrics.

If data is collected on paper and then typed into the system at a later stage, apart from the waste of this operation, the errors might be difficult to recover.

If we are able to simplify the data recording operation, the operator can report in real time all incidents or data entries, in which case the data collection application can automatically associate a Timestamp to each piece of data entered:

The operator just selects the incident from a drop down list and the time stamp is added automatically.

In the previous example by subtracting the two timestamps you know the line was stopped for 28 minutes.

Automatic Timestamp Insertion with Excel

The way to register a timestamp with Excel is:

When Excel is used for data collection in a portable or smartphone you can automatically add the timestamp with a formula the moment data is entered:

In this example weight is collected daily on a file in the cloud with a smartphone. The moment you enter your weight in column B the time stamp is automatically added in column A.
In this case I am recording my daily weight so I want to just show the date but the cell contains the full timestamp which I can show anytime just by changing the cell format.
From a timestamp X you can easily obtain:

You can also obtain the Shift (Morning, Afternoon, Night) from the hour of the Time Stamp with a formula.
This data obtained from the timestamp could be very useful during analysis in order to detect significant differences between:
  • Hours
  • Shifts/ operators
  • Days of the week
  • Weeks
  • Months

 Example of Timestamp Use for Data Analysis

Download this Excel file TimeStamp.xlsm from OneDrive 
In this example we have collected hourly production data in a line during a week. Timestamps have been added automatically with no extra effort by the operator.

We want to know if there is a significant difference among shifts or days of the week.
From the timestamp we extract the Day of the Week as shown above:

DOW = 1 is Monday.
Now we can use a Pivot Table to separate the production data into different columns for each DOW:


Now with Excel Analysis/ Anova:
We want to find out if there is a significant difference in the hourly production the different days of the week.

This output from my Spanish Excel you can see it in your own language using the file Timestamp.xlsm you just downloaded.
The p value is confirming what we already knew: at least one day is different.
This is obvious looking at the averages: Saturdays and Sundays have much lower production.
We now calculate the confidence intervals of the averages from this data:


We see that Fridays and Thursdays are significantly more productive than Mondays. This leads us to investigate the reason: Slow start of the week? Start with an empty line? etc.

Shift Effect on Productivity

Following the same process with shifts:

 We have obtained the Hour from the timestamp and shift with a formula from the hour.
Our Anova will result in:


In this case the p value is telling us that there are no significant differences among the 3 shifts in spite of the measured night average being lower than the morning one.
Looking at the confidence intervals:

The overlap confirms this.

Data Collection

Very often data is still being collected on paper because there are no terminals at the point of use and it would be very costly to install them.

Pencil & Paper Data Collection

On the other hand we all use cloud based technologies in our private life: smartphones, tablets and portables sharing data in the cloud, with basically no cost other than the WIFI/ Wideband connection.

A new approach of BYOD (Bring Your Own Device) is being practiced by some companies which makes data collection almost free by using the employees own smart phone or tablet with the Company WIFI and private cloud.

Smartphone Workplace Reporting

In the following example a spreadsheet is shared in Google Drive by line operators who report line incidents in real time with their phones and maintenance engineers who report the repair actions the moment they do it also with their phones.

Operators select the equipment, component, defect and severity with a single touch on each of the pull down menus. The current timestamp is entered automatically and the expected repair date is also calculated automatically from the severity (Tipo) and defect reported date.

If a repair date is missing and the expected repair date is exceeded cells become red to warn the maintenance engineers. The moment the maintenance engineer enters the repair action the timestamp is also entered automatically.

Lists of pending repairs are obtained from this table with pivot tables to inform the maintenance engineers of all pending repairs and their due dates and severity.

We can easily extract reports such as:
  • Average repair times per severity
  • Correlation between defects and repair actions
  • Frequency of defective components for each equipment


  • A timestamp is a key process piece of data we can collect for free
  • Paper recording can easily be replaced by widely used devices such as smartphones and tablets which require no operator training
  • Easy availability of these devices enables reporting in real-time and therefore real-time control of the process
  • This enables the automatic recording of timestamps associated to each piece of data the moment it is entered
  • Data entry is made much more simple and robust by eliminating data entry errors which are difficult and cumbersome to detect and correct at a later stage.
  • Very useful information such as day of the week, time, shift, etc. can be extracted from the stored timestamp. This becomes essential in process analysis and it could help us getting to the root cause of problems.


Popular posts from this blog

Excel VSM Simulator

Six Sigma Virtual Catapult

Excel Value Stream Map