Tips and Tricks for Date/Time Data

Whether you're a Lean Six Sigma black belt, a researcher, or a statistics student, at some point you will need to work with data that involve either dates, times, or both. Do you know where all of your date/time tools are? Let’s take a quick trip through Minitab Statistical Software and see what it has to offer for date/time data:

Column Indicators of Date and Time Data

You will know when Minitab recognizes a column as a date/time format if it has a “-D” to the right of the number, as in “C1-D”.  If Minitab recognizes the column as text data, you’ll see a “C1-T.” If it’s numeric data, nothing will appear to the right of C1.

Changing a Column into Date/Time Format

Minitab tries to determine column formats when you paste data into a worksheet, but sometimes it will mistake a date column for text. You can fix this a number of ways:

  1. You can change the data type prior to importing the data into Minitab. If the data comes from one of the file types listed under File > Open Worksheet, you can click on the “Preview” button located below “Files of type”, which will allow you to correct any misclassifications.
  1. If you've already imported your data, go to Data > Change Data Type. In this menu, you can select the format that matches what the data represents. If you want the data to be in a date/time format and Minitab currently treats it as text, select Text to Date/Time.

In this dialog window, you’ll be asked to specify what date/time format the data is in. Minitab Help includes a nice list of valid date/time components, which can be accessed by clicking on the Help button in the lower left corner of the dialog for Change Data Type > Text to Date/Time. Here is an excerpt from that list:

You’ll have to utilize these components to generate a format that Minitab understands. If the dates in your text column have values such as 2/2/2012, you would have to specify a format of 'm/d/yyyy' under Format of text columns in the dialog window.

Basic Tips for Formatting Date and Time Data

These are some tips that I have found very helpful in constructing date/time formats:

  • Default date (not time) components can be separated by (/), (-), or (.). All components must be separated by the same symbol (except for the default format mmmm d, yy).
  • Time  components are separated as follows: hh:mm:ss AM/PM
  • To indicate elapsed time, enclose the largest time unit in square brackets. If the format was set to [h]:mm:ss, Minitab would interpret 10:23 as 10 hours and 23 minutes. If the time format were [m]:ss, Minitab would interpret 10:23 as 10 minutes and 23 seconds.
  • For combining dates and time in one format, the date part must always appear first, and there must be a space between the date portion and the time portion.

Manipulating an Existing Date/Time Format

Once you’ve established a date/time format for a column, you might wish to change it so that it only shows mmm/yyyy instead of mmm/dd/yyyy. To do this, left-click to select any cell in the date/time column, then right-click and go to Format Column > Date/Time.

You’ll see a list of available formats to select from, and you’ll also have the opportunity to create a new format by typing it under the “New Format” box. If you are concerned about losing the information in the column after selecting a new format, don’t worry. This feature only changes the visibility of components in the worksheet, and doesn’t actually remove any information about the date/time. So the value for the day will remain a part of your data.

Minitab offers additional tools for handling date/time, like extractions, calculations, and coding. I'll cover those in my next post!  Meanwhile, I hope this post helps you the next time you are working with Minitab!


7 Deadly Statistical Sins Even the Experts Make

Do you know how to avoid them?

Get the facts >


Name: Omaina • Thursday, October 24, 2013

I want to show time greater than 24 hours in the format 33:45:09 (for example). Minitab 16 is not displaying times above 24 hours correctly. How do I fix that?

Name: Eston Martz • Thursday, October 24, 2013

Hi Omaina,
It sounds like you want to display elapsed time. To do this, you need to enclose the largest time unit in square brackets. For example, [h]:mm:ss indicates elapsed time in hours, minutes, and seconds, so Minitab interprets 10:23 as 10 hours and 23 minutes. If the time format were [m]:ss, Minitab would interpret 10:23 as 10 minutes and 23 seconds.
Hope this helps!

Name: Mark • Thursday, December 26, 2013

My data set includes date and time for multiple days. I'd like to examine the data that occurs in just a two hour period (7:30 AM to 9:30 AM) for all the days. Having trouble assigning the subset's time frame in a way Minitab recognizes.

Name: Eston Martz • Thursday, January 2, 2014

Hi Mark -
Please contact our support team either by phone or online at http://minitab.com/support -- they will be able to help you!

blog comments powered by Disqus